totn SQL

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 listed in a SQL statement.

There are 4 different types of SQL joins:

  • SQL INNER JOIN (sometimes called simple join)
  • SQL LEFT OUTER JOIN (sometimes called LEFT JOIN)
  • SQL RIGHT OUTER JOIN (sometimes called RIGHT JOIN)
  • SQL FULL OUTER JOIN (sometimes called FULL JOIN)

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

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

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 INNER JOIN in SQL 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:

SQL

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

Example

Let's look at an example of how to use the INNER JOIN in a query.

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

And a table called orders with the following data:

order_id customer_id order_date
1 7000 2016/04/18
2 5000 2016/04/18
3 8000 2016/04/19
4 4000 2016/04/20
5 NULL 2016/05/01

Enter the following SQL statement:

Try It
SELECT customers.customer_id, orders.order_id, orders.order_date
FROM customers 
INNER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

There will be 4 records selected. These are the results that you should see:

customer_id order_id order_date
4000 4 2016/04/20
5000 2 2016/04/18
7000 1 2016/04/18
8000 3 2016/04/19

This example would return all rows from the customers and orders tables where there is a matching customer_id value in both the customers and orders tables.

The rows where customer_id is equal to 6000 and 9000 in the customers table would be omitted, since they do not exist in both tables. The row where the order_id is 5 from the orders table would be omitted, since the customer_id of NULL does not exist in the customers table.

Old Syntax

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

Try It
SELECT customers.customer_id, orders.order_id, orders.order_date
FROM customers, orders
WHERE customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

SQL LEFT OUTER JOIN

Another type of join is called a LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the LEFT OUTER JOIN in SQL is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the OUTER keyword is omitted and written simply as LEFT JOIN.

Visual Illustration

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

SQL

The SQL LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

Example

Now let's look at an example that shows how to use the LEFT OUTER JOIN in a SELECT statement.

Using the same customers table as the previous example:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

And the orders table with the following data:

order_id customer_id order_date
1 7000 2016/04/18
2 5000 2016/04/18
3 8000 2016/04/19
4 4000 2016/04/20
5 NULL 2016/05/01

Enter the following SQL statement:

Try It
SELECT customers.customer_id, orders.order_id, orders.order_date
FROM customers 
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

There will be 6 records selected. These are the results that you should see:

customer_id order_id order_date
4000 4 2016/04/20
5000 2 2016/04/18
6000 NULL NULL
7000 1 2016/04/18
8000 3 2016/04/19
9000 NULL NULL

This LEFT OUTER JOIN example would return all rows from the customers table and only those rows from the orders table where the joined fields are equal.

If a customer_id value in the customers table does not exist in the orders table, all fields in the orders table will display as NULL in the result set. As you can see, the rows where customer_id is 6000 and 9000 would be included with a LEFT OUTER JOIN but the order_id and order_date fields display NULL.

SQL RIGHT OUTER JOIN

Another type of join is called a SQL RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the RIGHT OUTER JOIN in SQL is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the OUTER keyword is omitted and written simply as RIGHT JOIN.

Visual Illustration

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

SQL

The SQL RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.

Example

Now let's look at an example that shows how to use the RIGHT OUTER JOIN in a SELECT statement.

Using the same customers table as the previous example:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

And the orders table with the following data:

order_id customer_id order_date
1 7000 2016/04/18
2 5000 2016/04/18
3 8000 2016/04/19
4 4000 2016/04/20
5 NULL 2016/05/01

Enter the following SQL statement:

Try It
SELECT customers.customer_id, orders.order_id, orders.order_date
FROM customers 
RIGHT OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

There will be 5 records selected. These are the results that you should see:

customer_id order_id order_date
NULL 5 2016/05/01
4000 4 2016/04/20
5000 2 2016/04/18
7000 1 2016/04/18
8000 3 2016/04/19

This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the customers table where the joined fields are equal.

If a customer_id value in the orders table does not exist in the customers table, all fields in the customers table will display as NULL in the result set. As you can see, the row where order_id is 5 would be included with a RIGHT OUTER JOIN but the customer_id field displays NULL.

SQL FULL OUTER JOIN

Another type of join is called a SQL FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with NULL values in place where the join condition is not met.

Syntax

The syntax for the SQL FULL OUTER JOIN is:

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the OUTER keyword is omitted and written simply as FULL JOIN.

Visual Illustration

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

SQL

The SQL FULL OUTER JOIN would return the all records from both table1 and table2.

Example

Let's look at an example that shows how to use the FULL OUTER JOIN in a SELECT statement.

Using the same customers table as the previous example:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

And the orders table with the following data:

order_id customer_id order_date
1 7000 2016/04/18
2 5000 2016/04/18
3 8000 2016/04/19
4 4000 2016/04/20
5 NULL 2016/05/01

Enter the following SQL statement:

Try It
SELECT customers.customer_id, orders.order_id, orders.order_date
FROM customers 
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

There will be 7 records selected. These are the results that you should see:

customer_id order_id order_date
NULL 5 2016/05/01
4000 4 2016/04/20
5000 2 2016/04/18
6000 NULL NULL
7000 1 2016/04/18
8000 3 2016/04/19
9000 NULL NULL

This FULL OUTER JOIN example would return all rows from the orders table and all rows from the customers table. Whenever the joined condition is not met, a NULL value would be extended to those fields in the result set. This means that if a customer_id value in the customers table does not exist in the orders table, all fields in the orders table will display as NULL in the result set. Also, if a customer_id value in the orders table does not exist in the customers table, all fields in the customers table will display as NULL in the result set.

As you can see, the rows where the customer_id is 6000 and 9000 would be included but the order_id and order_date fields for those records contains a NULL value. The row where the order_id is 5 would be also included but the customer_id field for that record has a NULL value.