ohazot

       _                    _
  ___ | |__   __ _ _______ | |_
 / _ \| '_ \ / _` |_  / _ \| __|
| (_) | | | | (_| |/ / (_) | |_
 \___/|_| |_|\__,_/___\___/ \__|

sql — queries and usage.

QUERIES

select * will show all fields
select distinct means it will only show unique records

SELECT

(back to top)

show_only_specified_field_for_all_records

Multiple tables

All records matching multiple conditions	: `select * from TABLE where FIELD1 = VALUE1 and FIELD2 = VALUE2;`
All matching records from multiple tables	: `select * from TABLE1, TABLE2 where TABLE1.FIELD1 = TABLE2.FIELD2;`

alias

Set table alias to simplify use	: `select * from TABLE1 ALIAS1;`
Use the alias when selecting	: `select T1.FIELD1, T1.FIELD2 from TABLE1 T1;`

GROUPING

(back to top)

Select record count by group:
select FIELD, count(FIELD) from TABLE group by FIELD;
Select record count by group where count is greater than 1 :
select FIELD, count(FIELD) from TABLE group by FIELD having count(FIELD) > 1;

Grouping functions include: count, avg, min, max, sum. Example: select FIELD1, sum(FIELD2) from TABLE group by FIELD1;

LIMITS

(back to top)

Limit displayed records	: `select * from TABLE limit 10;`
Select 10 records starting from the 5th record	: `select * from TABLE limit 10 offset 5;`

JOINS

(back to top)

Joins data from multiple tables using [INNER] JOIN. JOIN implies INNER JOIN. Displays records matching all specified tables.

select
T1.FIELD1, T1.FIELD2,
T2.FIELD1, ALIAs2.FIELD2
from TABLE1 T1
join TABLE2 T2 on T2.FIELD = T1.FIELD;

outer join

Displays records matching any of the joined tables. Possible values are [LEFT|RIGHT|FULL] [OUTER] JOIN. Specifying OUTER is not necessary when using LEFT, RIGHT, FULL as it's implied.

left

Displays all records from the left table and matching records from the right table. Right is similar only showing records from right and matching from left.

select * from TABLE1 T1
right join TABLE2 T2 on T2.FIELD = T1.FIELD;

full

Shows all records from both tables.

CASE

(back to top)

Specify action based on CASE condition.

select
case
	when CONDITION1 then RESULT1
	when CONDITION2 then RESULT2
	else RESULT3
	end
from TABLE;

If field name is unspecified it will show as the query. To specify a field name:

select
case
	CONDITIONS
end as FIELD_NAME
from TABLE;

AUTHORS

(back to top)

ohazot | about | ohazot.com <admin@ohazot.com>