Home/Blog/Tutorials & Guides/What are SQL joins? Types of SQL joins explained
Home/Blog/Tutorials & Guides/What are SQL joins? Types of SQL joins explained

What are SQL joins? Types of SQL joins explained

Christina Kopecky
Nov 16, 2020
5 min read

Become a Software Engineer in Months, Not Years

From your first line of code, to your first day on the job — Educative has you covered. Join 2M+ developers learning in-demand programming skills.

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.

Master the fundamentals of SQL

Learn the basics of SQL such as how to create a database, how to insert, query, and update data.

An Introductory Guide to SQL

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 customer and a separate orders table:

svg viewer

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:

svg viewer

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 dataset that have matching values in both tables
  • LEFT (OUTER) JOIN: Returns all records from the left table and matched records from the right
  • 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
widget

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

Keep the learning going.#

Learn SQL without scrubbing through videos or documentation. Educative’s text-based courses are easy to skim and feature live coding environments, making learning quick and efficient.

An Introductory Guide to SQL


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;

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;

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 shows 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;

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, 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#

  

Free Resources

Learn in-demand tech skills in half the time

PRODUCTS

Mock Interview

New

Courses

Skill Paths

Projects

Assessments

TRENDING TOPICS

Learn to Code

Tech Interview Prep

Generative AI

Data Science

Machine Learning

GitHub Students Scholarship

Early Access Courses

Blind 75

Pricing

For Individuals

Try for Free

Gift a Subscription

CONTRIBUTE

Become an Author

Become an Affiliate

Earn Referral Credits

RESOURCES

Blog

Cheatsheets

Webinars

Answers

ABOUT US

Our Team

Careers

Hiring

Frequently Asked Questions

Contact Us

Press

LEGAL

Privacy Policy

Cookie Policy

Terms of Service

Business Terms of Service

Data Processing Agreement

INTERVIEW PREP COURSES

Grokking the Modern System Design Interview

Grokking the Product Architecture Design Interview

Grokking the Coding Interview Patterns

Machine Learning System Design

Tiktok Streamline Icon: https://streamlinehq.com

Copyright ©2024 Educative, Inc. All rights reserved.

soc2