SELECT

Definition:

To list a list.

---

Command Syntax:

1.Simple listing

SELECT [DISTINCT] field1_name [AS field1_new_name],field2_name... 
FROM table_name [table_new_name]
[WHERE criteria]
[GROUP BY fieldA_name,fieldB_name...] [HAVING criteria]
[ORDER BY fieldG_name DESC/[ASC], fieldH_name DESC/[ASC]...];

• Functions can be used
• WHERE criteria refers to WHERE - SQL statement
• Executing order:
  FROM>WHERE>GROUP BY
  >Aggregation Functions>HAVING
  >SELECT>DISTINCT>ORDER BY

2.Equi-join listing

SELECT [DISTINCT] table1_name.field1_name [AS field1_new_name],table2_name.field2_name...
FROM table1_name [table1_new_name],table2_name [table2_new_name],table3_name [table3_new_name]...
[WHERE table1_name.fieldA_name = table2_name.fieldB_name AND table1_name.fieldD_name = table3_name.fieldC_name AND criteria]
[GROUP BY fieldA_name,fieldB_name...]
[HAVING criteria] [ORDER BY fieldG_name DESC/[ASC], fieldH_name DESC/[ASC]...];

• Joined tables may have duplicate field name, so that it is better to write full field name(e.g. abc.field_name/table_name.field_name) in exam
• Functions can be used
• WHERE criteria refers to WHERE - SQL statement
• Executing order:
  FROM>WHERE>GROUP BY
  >Aggregation Functions>HAVING
  >SELECT>DISTINCT>ORDER BY

3.Difficult listing

SELECT [DISTINCT] table1_name.field1_name [AS field1_new_name],table2_name.field2_name...
FROM table1_name [table1_new_name] LEFT JOIN/RIGHT JOIN/FULL JOIN/INNER JOIN table2_name [table2_new_name] 
ON table1_name.fieldA_name = table2_name.fieldB_name 
LEFT JOIN/RIGHT JOIN/FULL JOIN/INNER JOIN table3_name [table3_new_name] ON table1_name.fieldD_name = table3_name.fieldC_name ... 
[WHERE criteria] 
[GROUP BY table2_name.fieldA_name,table1_name.fieldB_name...]
[HAVING criteria] [ORDER BY table1_name.fieldG_name DESC/[ASC], table2_name.fieldH_name DESC/[ASC]...]
[UNION/UNION ALL/INTERSECT/(EXCEPT/MINUS) SELECT...]
[UNION/UNION ALL/INTERSECT/(EXCEPT/MINUS) SELECT...]...;

• EXCEPT and MINUS are equivalent commands. The difference is that MySQL only supports EXCEPT command, the textbook is based on MySQL, and the HKDSE ICT candidates’ reference sheet only lists MINUS command.
• Joined tables may have duplicate field name, so that it is better to write full field name(e.g. abc.field_name/table_name.field_name) in exam
• Functions can be used
• WHERE criteria refers to WHERE - SQL statement
• Executing order:
  FROM>ON>OUTER JOIN>WHERE>GROUP BY
  >Aggregation Functions>HAVING
  >SELECT>DISTINCT>ORDER BY

---

Other Reference:

SQL order of execution:Link
MySQL:Link

---

'Long Long Long Long ago.'By secondary 6 student