SQL: Joins

This SQL tutorial explains how to use SQL joins with syntax, visual illustrations, and examples.

Description

SQL JOINS are used to retrieve data from multiple tables. A SQL JOIN is performed whenever two or more tables are joined in a SQL statement.

There are 4 different types of SQL joins:

·                     SQL INNER JOIN (or sometimes called simple join)

·                     SQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)

·                     SQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)

·                     SQL FULL OUTER JOIN (or sometimes called FULL JOIN)

So let's discuss SQL JOIN syntax, look at visual illustrations of SQL JOINS, and explore SQL JOIN examples.

SQL INNER JOIN (simple join)

Chances are, you've already written a SQL statement that uses an SQL INNER JOIN. It is the most common type of SQL join. SQL INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax

The syntax for the SQL INNER JOIN is:

SELECT columns

FROM table1 

INNER JOIN table2

ON table1.column = table2.column;

Visual Illustration

In this visual diagram, the SQL INNER JOIN returns the shaded area:

The SQL INNER JOIN would return the records where table1 and table2 intersect.

Example

Here is an example of a SQL INNER JOIN:

SELECT s.supplier_id, s.supplier_name, od.order_date

FROM suppliers AS s

INNER JOIN order_details AS od

ON s.supplier_id = od.supplier_id;

This SQL INNER JOIN example would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Let's look at some data to explain how the INNER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have another table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13
500127	10004	2003/05/14

If we run the SQL statement (that contains an INNER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers

INNER JOIN orders

ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13

The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables. The row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.

Old Syntax

As a final note, it is worth mentioning that the SQL INNER JOIN example above could be rewritten using the older implicit syntax as follows (but we still recommend using the INNER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers, orders

WHERE suppliers.supplier_id = orders.supplier_id;