What are SQL joins? Types of SQL joins explained
Tags: sql, programming, tutorial, productivity
Published: 2022-09-23 19:49:03

**Structured Query Language (SQL)** allows us to perform some sort of action on a single table in a relational database. These actions can update, create, delete or select a record in that table.

What if we had two tables that had different information about the same person, and we wanted to use all of that information to display on that person's invoice? We would need to use a **join clause** for that.

In this tutorial, we will define what a join clause is, talk about the types of join clauses, and give join examples for each.

* [What are SQL Joins?](#what)
* [Types of SQL Joins](#types)
* [Inner Joins](#inner)
* [Right Joins](#right)
* [Left Joins](#left)
* [Full Joins](#full)
* [What to learn next](#next)

<a name="what"></a>

## What are SQL Joins?

SQL join statements allow us to access information from two or more tables at once. They also keep our database normalized. Normalization allows us to keep data redundancy low so that we can decrease the amount of data anomalies in our application when we delete or update a record.

> **Simplified:** A JOIN clause allows us to combine rows from two or more tables based on a related column. 

Let's use the example above with our customer and the customer’s order to illustrate. If we had a Customers table that had information about our customers and a separate orders table:

![SQL joins](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1zeppcn9zmih2mtkdwui.png)

In these tables, take notice that there is a lot of the same information in both tables. A join statement greatly reduces the need for these duplicate values. Our new tables could look like this: 

![SQL table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eejd4e8icruhi5tn0r6j.png)

We can query the database by using join clauses to select information from the Customers table and information from the Orders table to use where we need to in our application. 

There are several different types of join statements depending on your needs. In the next section, we’ll take a look at examples of each type. 

<a name="types"></a>

## Types of Join statements

The type of join statement you use depends on your use case. There are four different types of join operations:

* **(INNER) JOIN:** Returns datasets that have matching values in both tables
* **LEFT (OUTER) JOIN:** Returns all records from the left table and matched records from the right s
* **RIGHT (OUTER) JOIN:** Returns all records from the right table and the matched records from the left 
* **FULL (OUTER) JOIN:** Returns all records when there is a match in either the left table or right table

![Inner joins](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mn1eqv7kgi1qqipru4oh.png)

<a name="inner"></a>

### Inner Joins

If you were to think of each table as a separate circle in a Venn diagram, the inner join would be the shaded area where **both circles intersect**. 

The INNER JOIN keyword selects all rows from the tables as long as a join condition satisfies. This keyword will create a result-set made up of combined rows from both tables where a common field exists.

Here is the syntax for an inner join:

```sql
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
```

This example will leave out any entries that have NULL values. 

```
create table Customers (
	customer_id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	address VARCHAR(50),
	city VARCHAR(50),
	state VARCHAR(50),
	zip_code VARCHAR(50),
	email VARCHAR(50),
    PRIMARY KEY(customer_id)
);

insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (1, 'Windham', 'McKevitt', '73 Namekagon Park', 'Washington', 'DC', '20525', 'wmckevitt0@example.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (2, 'Wallis', 'Breawood', '91869 Dexter Parkway', 'Pomona', 'CA', '91797', 'wbreawood1@nhs.uk');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (3, 'Valerie', 'Merkel', '35 Longview Alley', 'Bonita Springs', 'FL', '34135', 'vmerkel2@123-reg.co.uk');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (4, 'Brigid', 'Southwood', '568 Kropf Parkway', 'Indianapolis', 'IN', '46216', 'bsouthwood3@privacy.gov.au');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (5, 'Tucker', 'Du Pre', '521 Bonner Park', 'Miami', 'FL', '33175', 'tdupre4@thetimes.co.uk');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (6, 'Tabbie', 'Maxwaile', '3824 Sunnyside Pass', 'Pittsburgh', 'PA', '15261', 'tmaxwaile5@joomla.org');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (7, 'Cull', 'Stanborough', '8624 Larry Place', 'Boston', 'MA', '02298', 'cstanborough6@alibaba.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (8, 'Homer', 'Park', '8994 Elmside Circle', 'Dallas', 'TX', '75251', 'hpark7@plala.or.jp');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (9, 'Lorelei', 'Bollom', '5737 Manley Circle', 'San Francisco', 'CA', '94121', 'lbollom8@arstechnica.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (10, 'Claudina', 'Thibodeaux', '74 Holy Cross Hill', 'Norfolk', 'VA', '23520', 'cthibodeaux9@mysql.com');

create table Orders (
	order_id INT,
	order_date VARCHAR(50),
	amount VARCHAR(50),
	customer_id INT,
    PRIMARY KEY(order_id)
);

insert into Orders (order_id, order_date, amount, customer_id) values (34, '07-11-2020', '$56.34', 1);
insert into Orders (order_id, order_date, amount, customer_id) values (12, '07-12-2020', '$23.34', 2);
insert into Orders (order_id, order_date, amount, customer_id) values (18, '07-13-2020', '$156.72', 3);
insert into Orders (order_id, order_date, amount, customer_id) values (01, '07-14-2020', '$536.12', 4);
insert into Orders (order_id, order_date, amount, customer_id) values (89, '07-15-2020', '$99.45', 5);
insert into Orders (order_id, order_date, amount, customer_id) values (82, '07-12-2020', '$12.12', 6);
insert into Orders (order_id, order_date, amount, customer_id) values (99, '07-11-2020', '$19.52', 7);
insert into Orders (order_id, order_date, amount, customer_id) values (23, '07-11-2020', '$88.98', 2);
insert into Orders (order_id, order_date, amount, customer_id) values (22, '07-16-2020', '$222.34', 3);
insert into Orders (order_id, order_date, amount, customer_id) values (56, '07-16-2020', '$222.34', NULL);

select first_name, last_name, order_date, amount
from Customers c
inner join Orders o
on c.customer_id = o.customer_id
ORDER BY order_date

-->
first_name	last_name	order_date	amount
Wallis	Breawood	07-11-2020	$88.98
Windham	McKevitt	07-11-2020	$56.34
Cull	Stanborough	07-11-2020	$19.52
Wallis	Breawood	07-12-2020	$23.34
Tabbie	Maxwaile	07-12-2020	$12.12
Valerie	Merkel	07-13-2020	$156.72
Brigid	Southwood	07-14-2020	$536.12
Tucker	Du Pre	07-15-2020	$99.45
Valerie	Merkel	07-16-2020	$222.34
```

<a name="right"></a>

### Right outer Joins

This join statement takes all the records from Table B whether or not they have NULL values and the matching columns from Table A. 

Right join returns all the rows of the rightmost table of and the matching rows for the leftmost table. RIGHT JOIN is also known as RIGHT OUTER. Here is the syntax:

```sql
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
```

Here, our Customers table is Table A and the Orders table is Table B. 

```
create table Customers (
	customer_id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	address VARCHAR(50),
	city VARCHAR(50),
	state VARCHAR(50),
	zip_code VARCHAR(50),
	email VARCHAR(50),
    PRIMARY KEY(customer_id)
);

insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (1, 'Windham', 'McKevitt', '73 Namekagon Park', 'Washington', 'DC', '20525', 'wmckevitt0@example.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (2, 'Wallis', 'Breawood', '91869 Dexter Parkway', 'Pomona', 'CA', '91797', 'wbreawood1@nhs.uk');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (3, 'Valerie', 'Merkel', '35 Longview Alley', 'Bonita Springs', 'FL', '34135', 'vmerkel2@123-reg.co.uk');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (4, 'Brigid', 'Southwood', '568 Kropf Parkway', 'Indianapolis', 'IN', '46216', 'bsouthwood3@privacy.gov.au');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (5, 'Tucker', 'Du Pre', '521 Bonner Park', 'Miami', 'FL', '33175', 'tdupre4@thetimes.co.uk');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (6, 'Tabbie', 'Maxwaile', '3824 Sunnyside Pass', 'Pittsburgh', 'PA', '15261', 'tmaxwaile5@joomla.org');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (7, 'Cull', 'Stanborough', '8624 Larry Place', 'Boston', 'MA', '02298', 'cstanborough6@alibaba.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (8, 'Homer', 'Park', '8994 Elmside Circle', 'Dallas', 'TX', '75251', 'hpark7@plala.or.jp');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (9, 'Lorelei', 'Bollom', '5737 Manley Circle', 'San Francisco', 'CA', '94121', 'lbollom8@arstechnica.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (10, 'Claudina', 'Thibodeaux', '74 Holy Cross Hill', 'Norfolk', 'VA', '23520', 'cthibodeaux9@mysql.com');

create table Orders (
	order_id INT,
	order_date VARCHAR(50),
	amount VARCHAR(50),
	customer_id INT,
    PRIMARY KEY(order_id)
);

insert into Orders (order_id, order_date, amount, customer_id) values (34, '07-11-2020', '$56.34', 1);
insert into Orders (order_id, order_date, amount, customer_id) values (12, '07-12-2020', '$23.34', 2);
insert into Orders (order_id, order_date, amount, customer_id) values (18, '07-13-2020', '$156.72', 3);
insert into Orders (order_id, order_date, amount, customer_id) values (01, '07-14-2020', '$536.12', 4);
insert into Orders (order_id, order_date, amount, customer_id) values (89, '07-15-2020', '$99.45', 5);
insert into Orders (order_id, order_date, amount, customer_id) values (82, '07-12-2020', '$12.12', 6);
insert into Orders (order_id, order_date, amount, customer_id) values (99, '07-11-2020', '$19.52', 7);
insert into Orders (order_id, order_date, amount, customer_id) values (23, '07-11-2020', '$88.98', 2);
insert into Orders (order_id, order_date, amount, customer_id) values (22, '07-16-2020', '$222.34', 3);
insert into Orders (order_id, order_date, amount, customer_id) values (56, '07-16-2020', '$77.92', NULL);

select first_name, last_name, order_date, amount
from Customers c
right join Orders o
on c.customer_id = o.customer_id
ORDER BY order_date;

-->
first_name	last_name	order_date	amount
Wallis	Breawood	07-11-2020	$88.98
Windham	McKevitt	07-11-2020	$56.34
Cull	Stanborough	07-11-2020	$19.52
Wallis	Breawood	07-12-2020	$23.34
Tabbie	Maxwaile	07-12-2020	$12.12
Valerie	Merkel	07-13-2020	$156.72
Brigid	Southwood	07-14-2020	$536.12
Tucker	Du Pre	07-15-2020	$99.45
Valerie	Merkel	07-16-2020	$222.34
NULL	NULL	07-16-2020	$77.92
```

<a name="left"></a>

### Left outer Joins

Left join is similar to right join. Left join returns all the rows of the leftmost table and the matching rows for the rightmost table. Below is the syntax:

```sql
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
```

In this example, all of the records from the Customers table are listed (whether or not they have NULL values) along with the matching columns in the Orders table. 

```
create table Customers (
	customer_id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	address VARCHAR(50),
	city VARCHAR(50),
	state VARCHAR(50),
	zip_code VARCHAR(50),
	email VARCHAR(50),
    PRIMARY KEY(customer_id)
);

insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (1, 'Windham', 'McKevitt', '73 Namekagon Park', 'Washington', 'DC', '20525', 'wmckevitt0@example.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (2, 'Wallis', 'Breawood', '91869 Dexter Parkway', 'Pomona', 'CA', '91797', 'wbreawood1@nhs.uk');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (3, 'Valerie', 'Merkel', '35 Longview Alley', 'Bonita Springs', 'FL', '34135', 'vmerkel2@123-reg.co.uk');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (4, 'Brigid', 'Southwood', '568 Kropf Parkway', 'Indianapolis', 'IN', '46216', 'bsouthwood3@privacy.gov.au');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (5, 'Tucker', 'Du Pre', '521 Bonner Park', 'Miami', 'FL', '33175', 'tdupre4@thetimes.co.uk');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (6, 'Tabbie', 'Maxwaile', '3824 Sunnyside Pass', 'Pittsburgh', 'PA', '15261', 'tmaxwaile5@joomla.org');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (7, 'Cull', 'Stanborough', '8624 Larry Place', 'Boston', 'MA', '02298', 'cstanborough6@alibaba.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (8, 'Homer', 'Park', '8994 Elmside Circle', 'Dallas', 'TX', '75251', 'hpark7@plala.or.jp');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (9, 'Lorelei', 'Bollom', '5737 Manley Circle', 'San Francisco', 'CA', '94121', 'lbollom8@arstechnica.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (10, 'Claudina', 'Thibodeaux', '74 Holy Cross Hill', 'Norfolk', 'VA', '23520', 'cthibodeaux9@mysql.com');

create table Orders (
	order_id INT,
	order_date VARCHAR(50),
	amount VARCHAR(50),
	customer_id INT,
    PRIMARY KEY(order_id)
);

insert into Orders (order_id, order_date, amount, customer_id) values (34, '07-11-2020', '$56.34', 1);
insert into Orders (order_id, order_date, amount, customer_id) values (12, '07-12-2020', '$23.34', 2);
insert into Orders (order_id, order_date, amount, customer_id) values (18, '07-13-2020', '$156.72', 3);
insert into Orders (order_id, order_date, amount, customer_id) values (01, '07-14-2020', '$536.12', 4);
insert into Orders (order_id, order_date, amount, customer_id) values (89, '07-15-2020', '$99.45', 5);
insert into Orders (order_id, order_date, amount, customer_id) values (82, '07-12-2020', '$12.12', 6);
insert into Orders (order_id, order_date, amount, customer_id) values (99, '07-11-2020', '$19.52', 7);
insert into Orders (order_id, order_date, amount, customer_id) values (23, '07-11-2020', '$88.98', 2);
insert into Orders (order_id, order_date, amount, customer_id) values (22, '07-16-2020', '$222.34', 3);
insert into Orders (order_id, order_date, amount, customer_id) values (56, '07-16-2020', '$77.92', NULL);

select first_name, last_name, order_date, amount
from Customers c
left join Orders o
on c.customer_id = o.customer_id
ORDER BY order_date;

-->
first_name	last_name	order_date	amount
Claudina	Thibodeaux	NULL	NULL
Lorelei	Bollom	NULL	NULL
Homer	Park	NULL	NULL
Cull	Stanborough	07-11-2020	$19.52
Windham	McKevitt	07-11-2020	$56.34
Wallis	Breawood	07-11-2020	$88.98
Wallis	Breawood	07-12-2020	$23.34
Tabbie	Maxwaile	07-12-2020	$12.12
Valerie	Merkel	07-13-2020	$156.72
Brigid	Southwood	07-14-2020	$536.12
Tucker	Du Pre	07-15-2020	$99.45
Valerie	Merkel	07-16-2020	$222.34
```

<a name="full"></a>

### Full Joins

Full joins are also known as full outer joins. This basically means that a query would combine data and return records from both tables no matter if they had NULL values. 

FULL JOIN creates a result-set by combining the results of the left and right joins, including all the rows. For the rows that do not match. the result-set (joined table) will show NULL values. The syntax is as follows:

```
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
```

In PostgreSQL, the full join syntax works:

```
create table Customers (
	customer_id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	address VARCHAR(50),
	city VARCHAR(50),
	state VARCHAR(50),
	zip_code VARCHAR(50),
	email VARCHAR(50),
    PRIMARY KEY(customer_id)
);

insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (1, 'Windham', 'McKevitt', '73 Namekagon Park', 'Washington', 'DC', '20525', 'wmckevitt0@example.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (2, 'Wallis', 'Breawood', '91869 Dexter Parkway', 'Pomona', 'CA', '91797', 'wbreawood1@nhs.uk');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (3, 'Valerie', 'Merkel', '35 Longview Alley', 'Bonita Springs', 'FL', '34135', 'vmerkel2@123-reg.co.uk');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (4, 'Brigid', 'Southwood', '568 Kropf Parkway', 'Indianapolis', 'IN', '46216', 'bsouthwood3@privacy.gov.au');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (5, 'Tucker', 'Du Pre', '521 Bonner Park', 'Miami', 'FL', '33175', 'tdupre4@thetimes.co.uk');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (6, 'Tabbie', 'Maxwaile', '3824 Sunnyside Pass', 'Pittsburgh', 'PA', '15261', 'tmaxwaile5@joomla.org');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (7, 'Cull', 'Stanborough', '8624 Larry Place', 'Boston', 'MA', '02298', 'cstanborough6@alibaba.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (8, 'Homer', 'Park', '8994 Elmside Circle', 'Dallas', 'TX', '75251', 'hpark7@plala.or.jp');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (9, 'Lorelei', 'Bollom', '5737 Manley Circle', 'San Francisco', 'CA', '94121', 'lbollom8@arstechnica.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (10, 'Claudina', 'Thibodeaux', '74 Holy Cross Hill', 'Norfolk', 'VA', '23520', 'cthibodeaux9@mysql.com');
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (11, NULL, NULL , '74 Holy Cross Hill', 'Norfolk', 'VA', '23520', NULL);

create table Orders (
	order_id INT,
	order_date VARCHAR(50),
	amount VARCHAR(50),
	customer_id INT,
    PRIMARY KEY(order_id)
);

insert into Orders (order_id, order_date, amount, customer_id) values (98, '07-01-2020', '$333.33', 11);
insert into Orders (order_id, order_date, amount, customer_id) values (12, '07-12-2020', '$23.34', 2);
insert into Orders (order_id, order_date, amount, customer_id) values (18, '07-13-2020', '$156.72', 3);
insert into Orders (order_id, order_date, amount, customer_id) values (01, '07-14-2020', '$536.12', 4);
insert into Orders (order_id, order_date, amount, customer_id) values (89, '07-15-2020', '$99.45', 5);
insert into Orders (order_id, order_date, amount, customer_id) values (82, '07-12-2020', '$12.12', 6);
insert into Orders (order_id, order_date, amount, customer_id) values (99, '07-11-2020', '$19.52', 7);
insert into Orders (order_id, order_date, amount, customer_id) values (23, '07-11-2020', '$88.98', 2);
insert into Orders (order_id, order_date, amount, customer_id) values (22, '07-16-2020', '$222.34', 3);
insert into Orders (order_id, order_date, amount, customer_id) values (56, '07-16-2020', '$77.92', NULL);
insert into Orders (order_id, order_date, amount, customer_id) values (34, '07-11-2020', '$56.34', 1);

select first_name, last_name, order_date, amount
from Customers c
full join Orders o
on c.customer_id = o.customer_id
ORDER BY order_date;

-->
first_name	last_name	order_date	amount
		07-01-2020	$333.33
Wallis	Breawood	07-11-2020	$88.98
Cull	Stanborough	07-11-2020	$19.52
Windham	McKevitt	07-11-2020	$56.34
Tabbie	Maxwaile	07-12-2020	$12.12
Wallis	Breawood	07-12-2020	$23.34
Valerie	Merkel	07-13-2020	$156.72
Brigid	Southwood	07-14-2020	$536.12
Tucker	Du Pre	07-15-2020	$99.45
		07-16-2020	$77.92
Valerie	Merkel	07-16-2020	$222.34
Lorelei	Bollom		
Claudina	Thibodeaux		
Homer	Park		
```

> **Note:** Full joins are not typically used, which may explain why MySQL doesn’t have support for one. There are some use cases, however.
>
> For example, with view entries where an order is not associated with a customer or a customer that has not made any orders.

<a name="next"></a>

## What to learn next

Congrats on learning how to do Joins in SQL. This simple skill can make your SQL coding a whole lot easier. But there is still more to learn. The next step to take are:
* Cross join
* Joins with wildcard
* Joins on foreign keys
* Advanced SQL

To get started on these concepts, check out Educative's SQL tutorial **[An Introductory Guide to SQL](https://www.educative.io/courses/introductory-guide-to-sql?eid=5082902844932096)**. You’ll start by covering the basics of SQL such as how to create a database, and how to insert, query, and update data. Throughout, you’ll get to execute SQL queries in your browser and see results in real-time.

*Happy learning!*

### Continue reading about SQL on Educative
* [The complete guide to system design in 2022](https://www.educative.io/blog/complete-guide-to-system-design?eid=5082902844932096)
* [What are database schemas? 5-minute guide with examples
](https://www.educative.io/blog/what-are-database-schemas-examples?eid=5082902844932096)
* [What is a Database Query? SQL and NoSQL queries explained
](https://www.educative.io/blog/what-is-database-query-sql-nosql?eid=5082902844932096)
* [Database Design Tutorial
](https://www.educative.io/blog/database-design-tutorial?eid=5082902844932096)

### Start a discussion 
Which SQL tutorial would you like to read next? Was this article helpful? Let us know in the comments below!