SQL Joins

The "Persons" table:

P_Id LastName FirstName Address         City
1 Hansen     Ola        Timoteivn 10  Sandnes
2 Svendson   Tove       Borgvn 23     Sandnes
3 Pettersen  Kari       Storgt 20     Stavanger

The "Orders" table:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

INNER JOIN
"List all the persons with any orders"

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName FirstName OrderNo
Hansen     Ola     22456
Hansen     Ola     24562
Pettersen  Kari    77895
Pettersen  Kari    44678

LEFT JOIN / LEFT OUTER JOIN
The LEFT JOIN keyword returns all the rows from the left table (table_name1), even if there are no matches in the right table (table_name2)

"List all the persons and their orders - if any"

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName FirstName OrderNo
Hansen     Ola     22456
Hansen     Ola     24562
Pettersen  Kari    77895
Pettersen  Kari    44678
Svendson Tove      (null)

The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).

RIGHT JOIN / RIGHT OUTER JOIN
The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1)

"List all the orders with containing persons - if any"

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName FirstName OrderNo
Hansen     Ola     22456
Hansen     Ola     24562
Pettersen  Kari    77895
Pettersen  Kari    44678
(null)    (null)   34764

The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).