SQL Join

Joins and Keys

Sometimes we have to select data from two tables to make our result complete. We have to perform a join.

Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table.

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

When you look on the example tables below, notice that: 

• The "ID" column is the primary key of the "Employees" table
• The "ID" column in the "Orders" table is used to refer to the persons in the "Employees" table without using their names

Employees:

ID	Name
01	Hansen, Ola
02	Svendson, Tove
03	Svendson, Stephen
04	Pettersen, Kari

Orders:

ID	Product
01	Printer
03	Table
03	Chair

Referring to Two Tables

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

Example

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

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

Result

Name	Product
Hansen, Ola	Printer
Svendson, Stephen	Table
Svendson, Stephen	Chair

Example

Who ordered a printer?

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

Result

Name
Hansen, Ola

Using Joins

OR, we can select data from two tables with the JOIN keyword, like this (this is the prefered way to do it):

Example INNER JOIN

Syntax

SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

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

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

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.

Result

Name	Product
Hansen, Ola	Printer
Svendson, Stephen	Table
Svendson, Stephen	Chair

Example LEFT JOIN

Syntax

SELECT field1, field2, field3 FROM first_table LEFT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

List all employees, and their orders - if any.

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

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.

Result

Name	Product
Hansen, Ola	Printer
Svendson, Tove
Svendson, Stephen	Table
Svendson, Stephen	Chair
Pettersen, Kari

Example RIGHT JOIN

Syntax

SELECT field1, field2, field3 FROM first_table RIGHT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

List all orders, and who has ordered - if any.

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

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

Result

Name	Product
Hansen, Ola	Printer
Svendson, Stephen	Table
Svendson, Stephen	Chair

Example

Who ordered a printer?

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

Result

Name
Hansen, Ola