SQL ORDER BY

The ORDER BY keyword is used to sort the result.

 SELECT column_name(s) FROM table_name
 ORDER BY column_name(s)

NOTE: Including more than 1 column_name after "ORDER BY" statement, sorts the first stated column and then the next columns ONLY IF there are duplicated values like the "W3schools" in the 1st sort column (in "Orders" Table below) or IF there are a handful of nulls.


Example "Orders" table

Company OrderNumber
Sega 3412
ABC Shop 5678
W3Schools 6798
W3Schools 2312

Code Sorts Company Name in ASCending [default] order)
 SELECT Company, OrderNumber FROM Orders
 ORDER BY Company

Code Sorts Company in DESCending order and OrderNumber in ASCending order
 SELECT Company, OrderNumber FROM Orders
 ORDER BY Company DESC, OrderNumberASC

Result-Set of latter code:

Company OrderNumber
W3Schools 2312
W3Schools 6798
Sega 3412
ABC Shop 5678


SQL AND & OR Operators

AND and OR join two or more conditions in a WHERE clause.

AND operator - displays a Record (row) if ALL conditions listed are true
OR operator - displays a Record (row) if ANY of the conditions listed are true.


Example Statement:

 SELECT column_name(s) FROM table_name
 WHERE (column_name1=Value1 OR column_name2=Value2)
 AND column_name3=Value3


SQL IN Operator

The IN Operator may be used if you know the exact value you want to return for at least one of the columns.

 SELECT column_name(s) FROM table_name
 WHERE column_name IN (value1,value2,..)


Example Statement:

 SELECT * FROM Persons
 WHERE LastName IN ('Gutierrez','Kunz')

Displays the 2 rows: one with 'Gutierrez' and the other with 'Kunz'


SQL BETWEEN...AND Operator

The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates.

 SELECT column_name(s) FROM table_name 
 WHERE column_name
 BETWEEN value1 AND value2


To display in "Persons" table the persons alphabetically between (and including) "Gutierrez" and exclusive "Kyla", use the following SQL:

 SELECT FROM Persons 
 WHERE LastName
 BETWEEN 'Gutierrez' AND 'Kyza'

This displays persons from 'Gutierrez' G,H,I,J and LastNames BEFORE 'Kyla' like 'Kunz' because 'Ku' is before 'Ky'


To display the persons outside the range used in the previous example, use the NOT operator:

 SELECT FROM Persons 
 WHERE LastName
 NOT BETWEEN 'Gutierrez' AND 'Kyza'


IMPORTANT! The BETWEEN...AND operator is treated differently in different databases. With some databases a person with the LastName of "Gutierrez" or "Kyla" will not be listed (BETWEEN..AND only selects fields that are between and excluding the test values). With some databases a person with the last name of "Gutierrez" or "Kyla" will be listed (BETWEEN..AND selects fields that are between and including the test values). With other databases a person with the last name of "Gutierrez" will be listed, but "Kyla" will not be listed (BETWEEN..AND selects fields between the test values, including the first test value and excluding the last test value). Therefore: Check how your database treats the BETWEEN....AND operator!


SQL Alias

With SQL, aliases can be used for column names and table names.

Column Name Alias Syntax

 SELECT column_name AS column_alias
 FROM table_name 

Table Name Alias Syntax

 SELECT column_name(s)
 FROM table_name AS table_alias

Sample "Persons" Table

LastName FirstName Address City
Gutierrez Hanna SanF 10 Millbrae
Kunz Harry Dauin 44 Hessen

 SELECT LastName AS Family,FirstName AS Name
 FROM table_name 

Result-Set of code: ("Persons" Table)

Family Name
Gutierrez Hanna
Kunz Harry


 SELECT LastName, FirstName
 FROM Persons AS Employees

Result-Set of code: ("Employees" Table)

LastName FirstName
Gutierrez Hanna
Kunz Harry



SQL JOINS & KEYS

two possible ways: (1)Referring to Two Tables; and (2)By use of JOIN Keyword


Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join.Tables in a database can be related to each other with keys.

primary key - is a column with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

In the "Employees" table below, the "Employee_ID" column is the primary key, meaning that NO two rows can have the same Employee_ID. The Employee_ID distinguishes two persons even if they have the same name.

Employees:

Employee_ID Name
01 Kunz, Harry Roland
02 Svendson, Tove
03 Gutierrez, Hanna
04 Pettersen, Kari

Orders:

Prod_ID Product Employee_ID
234 Printer 01
657 Table 03
865 Chair 03


Referring to Two Tables (Examples)

We can select data from two tables by referring to two tables, like this:

Who has ordered a product, and what did they order?

 SELECT Employees.Name, Orders.Product
 FROM Employees, Orders
 WHERE Employees.Employee_ID=Orders.Employee_ID

Result-Set:

Name Product
Kunz, Harry Roland Printer
Gutierrez, Hanna Table
Gutierrez, Hanna Chair

Who ordered a printer?

 SELECT Employees.Name
 FROM Employees, Orders 
 WHERE Employees.Employee_ID=Orders.Employee_ID
 AND Orders.Product='Printer'

Result-Set:

Name
Kunz, Harry Roland

INNER JOIN

Or we can select data from two tables with the JOIN keyword, like this:

 SELECT field1, field2, ... ,field(n)
 FROM first_table
 INNER JOIN second_table
 ON first_table.keyfield = second_table.foreign_keyfield

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.


Who has ordered a product, and what did they order?

 SELECT Employees.Name, Orders.Product
 FROM Employees
 INNER JOIN Orders
 ON Employees.Employee_ID=Orders.Employee_ID

Result-Set:

Name Product
Kunz, Harry Roland Printer
Gutierrez, Hanna Table
Gutierrez, Hanna Chair

Who ordered a printer?

 SELECT Employees.Name
 FROM Employees
 INNER JOIN Orders
 ON Employees.Employee_ID=Orders.Employee_ID
 WHERE Orders.Product='Printer'

Result-Set:

Name
Kunz, Harry Roland

LEFT JOIN

 SELECT field1, field2, ... ,field(n)
 FROM first_table
 LEFT JOIN second_table
 ON first_table.keyfield = second_table.foreign_keyfield

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.


List all employees, and their orders - if any.

 SELECT Employees.Name, Orders.Product
 FROM Employees
 LEFT JOIN Orders
 ON Employees.Employee_ID=Orders.Employee_ID

Result-Set:

Name Product
Kunz, Harry Printer
Svendson, Tove  
Gutierrez, Hanna Table
Gutierrez, Hanna Chair
Pettersen, Kari  

RIGHT JOIN

 SELECT field1, field2, ... ,field(n)
 FROM first_table
 RIGHT JOIN second_table
 ON first_table.keyfield = second_table.foreign_keyfield

The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.


 SELECT Employees.Name, Orders.Product
 FROM Employees
 RIGHT JOIN Orders
 ON Employees.Employee_ID=Orders.Employee_ID

Result-Set:

Name Product
Kunz, Harry Roland Printer
Gutierrez, Hanna Table
Gutierrez, Hanna Chair

SQL UNION and UNION ALL

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.

Note: With UNION, only distinct values are selected while With UNION ALL, all values are selected including duplicated values.

 SQL Statement 1
 UNION
 SQL Statement 2
 SQL Statement 1
 UNION ALL
 SQL Statement 2

Employees_Germany:

E_ID E_Name
01 Kunz, Harry Roland
02 Svendson, Tove
03 Gutierrez, Hanna
04 Pettersen, Kari

Employees_USA:

E_ID E_Name
01 Turner, Sally
02 Kent, Clark
03 Gutierrez, Hanna
04 Scott, Stephen

List all different employee names in Germany and USA:

 SELECT E_Name FROM Employees_Germany
 UNION
 SELECT E_Name FROM Employees_USA

Result-set:

E_Name
Kunz, Harry Roland
Svendson, Tove
Gutierrez, Hanna
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen

Note: This command cannot be used to list all employees in Germany and USA. In the example above we have two employees with equal names (Gutierrez, Hanna), and only one of them is listed. The UNION command only selects distinct values.


List all employees in Germany and USA:

 SELECT E_Name FROM Employees_Germany
 UNION ALL
 SELECT E_Name FROM Employees_USA

Result-set:

E_Name
Kunz, Harry Roland
Svendson, Tove
Gutierrez, Hanna
Pettersen, Kari
Turner, Sally
Kent, Clark
Gutierrez, Hanna
Scott, Stephen

Note: The UNION ALL command is equal to the UNION command, except that UNION ALL selects ALL values.