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?
- Types of SQL Joins
- Inner Joins
- Right Joins
- Left Joins
- Full Joins
- What to learn next
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:
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:
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.
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
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:
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
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:
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
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:
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
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.
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. 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
- What are database schemas? 5-minute guide with examples
- What is a Database Query? SQL and NoSQL queries explained
- Database Design Tutorial
Start a discussion
Which SQL tutorial would you like to read next? Was this article helpful? Let us know in the comments below!
Top comments (1)
In 20 years of writing SQL code I have never come across a legitimate reason to use Right Join in a production level query. It is a code -smell and indicates that you don't understand your data.
In the body of your article you briefy touch on normalisation which is a good thing and you later talk about orders without a customer which is a bad thing because it indicates that your database does not have referential integrity ( i.e you should not have been able to delete the customer because it has related order records)
You also failed to mention CROSS JOIN which is where e two datasets are unrelated but you need a matrix of all possible combinations. A typical use for a cross join would be to cross join a calendar table with the retail outlets table to create a retail outlet sales day record irrespective of whether there were any sales for that store for that day.