What Is FULL JOIN in SQL? An Explanation with 4 Examples

What is FULL JOIN – one of the often neglected SQL join types? In this article, we’ll show you how useful FULL JOIN  can be and teach you how to apply it to different scenarios. The four examples cover some of the typical uses.

An SQL join is a construction for combining data from two or more tables. FULL JOIN is one of the types of joins. You’ll get the most from this article if you’re already familiar with SQL joins and how they work. If you’re not or if your knowledge needs refreshing, we recommend taking our interactive SQL JOINs course to fill in the gaps. Ninety-three exercises await you there, where you’ll recap join types and practice filtering data using single and multiple joins, self-joins, and non-equi joins.

What Is a FULL JOIN?

FULL JOIN or FULL OUTER JOIN (SQL accepts both) is an outer join. In SQL, an outer join is a type of join that includes unmatched rows from one or both joined tables; LEFT JOIN and RIGHT JOIN are also outer joins. FULL JOIN is a union of a LEFT JOIN and RIGHT JOIN: it shows the matching and the non-matching rows from both tables. When the values from one table are not matched in the other table, FULL JOIN returns NULLs.  Visually, it can be shown like this:

FULL JOIN Syntax

The FULL JOIN syntax is:

SELECT …
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

As usual with SQL joins, the FULL JOIN syntax's main parts are the JOIN and ON clauses. The first table in the FROM clause is followed by the FULL JOIN keyword, which joins the second table. The condition on which the tables are joined is specified in the ON clause. Usually, tables are joined on a column that’s shared between them – e.g. the id column in the writer table and the writer_ID column in the book table. These two columns would have the same values because of the primary key – foreign key relationship.

In a simple example, we could write this code and get the following result:

Other Types of Joins in SQL

We already mentioned that FULL JOIN is only one of the several types of joins in SQL.  Here’s the full list:

• (INNER) JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN

JOIN (or INNER JOIN) is an inner type of join that returns only the matching rows from the joined tables. You can learn more about INNER JOIN here.

LEFT JOIN (or LEFT OUTER JOIN) lists all the rows from the first (left) table and only the matching rows from the second (right) table. If there are no matching rows in the right table, the values are shown as NULL. You can find more in our article about LEFT JOIN.

RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image of the LEFT JOIN. It returns all the rows from the second (right) table and only the matching rows from the first (left) table. Where there are non-matching rows in the left table, the values are NULL.

Now you can better understand why we said the FULL JOIN is the union of LEFT JOIN and RIGHT JOIN. As it shows all the data from both left and right tables and also the non-matching rows, it’s like you use LEFT JOIN and RIGHT JOIN at the same time.

CROSS JOIN returns a Cartesian product. In other words, it returns all the combinations of all the rows from both joined tables.

FULL JOIN Examples

Let’s now move on to the examples that showcase the typical use of FULL JOIN.

Example 1: Show All the Company’s Employees and Departments

Task: You need to show all employees in a company, even if they don’t have an assigned department. You also need to show all the departments, even if no employees are allocated to them.

Dataset: We’ll use two tables to solve the problem. The first table is department (the script is here), which is a list of the company’s departments:

id	department_name
1	IT
2	Accounting
3	Sales

The second table is employees, with the data shown below. The script is here:

id	first_name	last_name	department_id
1	Sarah	Zimmerman	2
2	Thomas	Tyson	1
3	Daniel	Richardson	1
4	Sofia	Tardelli	2
5	Mark	Fitzpatrick	4

Solution:

SELECT 
  first_name,
  last_name,
  department_name
FROM employees e
FULL JOIN department d
  ON e.department_id = d.id;

Explanation: The code selects employees’ first and last names and the department name.

We can do that because we FULL JOIN the two tables. We reference the table employees in FROM and give it an alias. Then, we reference the second table, department, in FULL JOIN. The tables are joined on the department IDs found in both tables. This joining condition is written in the ON clause.

Output: The output returns all data from both tables. There are some interesting rows that need elaboration.

Mark Fitzpatrick has no department – his department_id value is 5, but there’s no department with ID 5 in the table department. Thus, there’s a NULL value under the department name. This could be because he is a new employee and the employees table isn’t updated with the department data.

Also, the last row shows the Sales department, but no employee name. This indicates that Sales has no employees. Nothing unusual here, as we heard that all three employees from Sales recently quit the company.

first_name	last_name	department_name
Sarah	Zimmerman	Accounting
Thomas	Tyson	IT
Daniel	Richardson	IT
Sofia	Tardelli	Accounting
Mark	Fitzpatrick	NULL
NULL	NULL	Sales

Example 2: Show All Customers With the Number of Bought Products and the Number of Products Without Sales

Task: There’s a web record store that sells vinyls (i.e. music records). Your task is to list all the customers and find the number of vinyls each customer bought. The output also needs to show the number of vinyls nobody bought yet.

Dataset: We’ll work with three tables. The first is customer, with the script here:

id	first_name	last_name
1	Marvin	Simmons
2	Marianne	Dickens
3	Susan	Strozzi

You can find the script for the second table, vinyl, here. The table shows the list of vinyls, as you can see below:

id	artist	album_name	price
1	Callier, Terry	What Color is Love	24.99
2	Guy, Buddy	Sweet Tea	32.99
3	Little Simz	A Curious Tale of Trials	32.99
4	LaVette, Bettye	Scene of the Crime	36.99

The third table is a junction table named purchase. You can find the script here:

id	customer_id	vinyl_id	purchase_date
1	1	2	2023-01-03
2	1	3	2023-01-12
3	1	2	2023-02-18
4	1	3	2023-03-01
5	2	3	2023-03-01
6	2	2	2023-04-01
7	2	4	2023-05-01

Solution:

SELECT 
  first_name,
  last_name,
  COUNT (v.id) AS vinyl_count	   
FROM customer c
FULL JOIN purchase p
  ON c.id = p.customer_id
FULL JOIN vinyl v
  ON p.vinyl_id = v.id
GROUP BY first_name, last_name;

Explanation: This is an example of using FULL JOIN as well as joining more than two tables.

The query selects customer names. Then, it uses the COUNT() aggregate function to find the number of vinyls purchased.  We FULL JOIN the customer table with the purchase table. This is done on the customer IDs in both tables.

Now, we need to join the third table. It’s simple: write FULL JOIN again and reference the table vinyl. Now, you can join it with the table purchase on the vinyl IDs.

Output: The output shows there’s one vinyl that nobody has ordered yet. You can recognize it by the NULLs.

It also shows a list of all customers and the number of vinyls they bought. Susan Strozzi didn’t buy anything. Marianne Dickens and Marvin Simmons bought three and four vinyls, respectively.

first_name	last_name	vinyl_count
NULL	NULL	1
Susan	Strozzi	0
Marianne	Dickens	3
Marvin	Simmons	4

Example 3: List All the Student-Certification Pairs and the Students’ Grades

Task: You work for an online SQL certification platform. It offers several certifications; every year, there’s a new edition of every certification. Find all the possible student-certification pairs and the grade each student got in all certification editions.

Dataset: The dataset is increasing; it includes four tables now. The first table is student; here’s the script. It’s a list of the platform’s students:

our vinyls, respectively.

id	first_name	last_name
1	Tom	Frank
2	Mary	Maddison
3	Pavel	Kuba
4	Amanda	Wilson

The second table is the certificate table, which holds the list of certificates. Here’s the script:

id	certificate_name
1	Microsoft Certified: Azure Data Fundamentals
2	Oracle Database SQL Certified Associate Certification
3	IBM Certified Database Associate
4	MySQL 5.7 Database Administrator Certification
5	EDB PostgreSQL 12 Associate Certification

The next table is a junction table called certificate_enrollment

id	student_id	edition_id	grade	pass
1	2	1	620	FALSE
2	2	6	850	TRUE
3	2	10	900	TRUE
4	1	2	100	FALSE
5	1	7	500	FALSE
6	1	7	800	TRUE
7	4	8	800	TRUE

. You can find the script here. The table shows which students enrolled in which certificate, along with their grades and whether they passed:

The last table is a junction table named certificate_edition. It shows the list of certificate editions, linking to the table certificate. The script is here:

id	certificate_id	edition
1	1	2022
2	2	2022
3	3	2022
4	4	2022
5	5	2022
6	1	2023
7	2	2023
8	3	2023
9	4	2023
10	5	2023

Solution:

SELECT 
  first_name,
  last_name,
  certificate_name,
  edition,
  grade
FROM student s
FULL JOIN certificate_enrollment cen
  ON s.id = cen.student_id
FULL JOIN certificate_edition ced
  ON cen.edition_id = ced.id
FULL JOIN certificate c
  ON ced.certificate_id = c.id;

Explanation: We first select all the relevant columns from all four tables: first_name and last_name from student, certificate_name from certificate, edition from certificate_edition, and grade from certificate_enrollment.

Then, we join the tables like in previous examples. First, it’s the student table FULL JOINed with certificate_enrollment on the student IDs.

The second FULL JOIN adds the certificate_edition table to join it with certificate_enrollment. The tables are joined on the certificate edition ID.

Now that we have joined three tables, we can add the fourth one. We reference the table certificate in FULL JOIN. We join it with certificate_edition on the certificate ID.

Output: The output shows exactly what we want.

There’s a list of all the students who received a certificate, its edition, and the grade each student got. If the grade is below 700, then the student failed and had to retake the certificate exam.

One student hasn’t enrolled in a certificate program yet; it’s Pavel Kuba. Also, there are four certificate editions without any enrolled students.

first_name	last_name	certificate_name	edition	grade
Mary	Maddison	Microsoft Certified: Azure Data Fundamentals	2022	620
Mary	Maddison	Microsoft Certified: Azure Data Fundamentals	2023	850
Mary	Maddison	EDB PostgreSQL 12 Associate Certification	2023	900
Tom	Frank	Oracle Database SQL Certified Associate Certification	2022	100
Tom	Frank	Oracle Database SQL Certified Associate Certification	2023	500
Tom	Frank	Oracle Database SQL Certified Associate Certification	2023	800
Amanda	Wilson	IBM Certified Database Associate	2023	800
Pavel	Kuba	NULL	NULL	NULL
NULL	NULL	EDB PostgreSQL 12 Associate Certification	2022	NULL
NULL	NULL	MySQL 5.7 Database Administrator Certification	2022	NULL
NULL	NULL	IBM Certified Database Associate	2022	NULL
NULL	NULL	MySQL 5.7 Database Administrator Certification	2023	NULL

Example 4: Find All the Writer-Topic Pairs and Show the Articles and Their Status

Task: You work for a company that hires article writers for their customers. There’s a list of writers and topics you offer. Also, each topic currently has several available articles. The writers are assigned to these articles. An article can have one of two statuses: ‘Writing’ (the article is being written) or ‘Revising’ (the article is being revised).

Find all the writer-topic pairs. Additionally, list all the articles and their status.

Dataset: This dataset also consists of four tables. The first table is writer, with the script linked here:

id	first_name	last_name
1	Victoria	Thompson
2	Mike	McGill
3	Sky	Herrera
4	Jimmy	Goodman

The next table is topic. Here’s the script:

id	topic_name
1	SQL
2	Python
3	ML
4	SQL Careers
5	Python Careers

The third table is article_assignment. It links the writer with the article and shows the article's current status. Here’s the script:

id	writer_id	article_id	status
1	4	3	Revising
2	4	1	Writing
3	3	5	Writing
4	3	8	Revising
5	3	10	Revising
6	1	7	Writing

The fourth table links the article with the topic. It’s named article, and the script is here:

id	article_title	topic_id
1	What is FULL JOIN in SQL? An Explanation with 4 Examples	1
2	Pandas in Python	2
3	Supervised Learning	3
4	Basic SQL Interview Questions	4
5	Basic Python Interview Questions	5
6	SQL Window Functions	1
7	Ranking Data in Python	2
8	Unsupervised Learning	3
9	Intermediate SQL Interview Questions	4
10	Intermediate Python Interview Questions	5

Solution:

SELECT 
  first_name,
  last_name,
  topic_name,
  article_title,
  status
FROM writer w
FULL JOIN article_assignment aa
  ON w.id = aa.writer_id
FULL JOIN article a
  ON aa.article_id = a.id
FULL JOIN topic t
  ON a.topic_id = t.id;

Explanation: The query is similar to the previous one. It selects all the relevant columns – the writer's name, topic, article title, and its status. After that, it joins the tables writer and article_assignment on the writer ID using FULL OUTER JOIN.

Another FULL JOIN adds the third table article and joins it on the article ID. Finally, the fourth table is joined on the topic ID.

Output: Here’s a list of all the writers, the topics, articles, and statuses.

Jimmy Goodman’s topics are ML and SQL. Sky Herrera writes about Python Careers and ML. Victoria Thompson writes only about Python.

Mike McGill doesn’t have any assigned articles. Also, there are one Python, one SQL, and two SQL Career articles unassigned to any writer.

first_name	last_name	topic_name	article_title	status
Jimmy	Goodman	ML	Supervised Learning	Revising
Jimmy	Goodman	SQL	What is FULL JOIN in SQL? An Explanation with 4 Examples	Writing
Sky	Herrera	Python Careers	Basic Python Interview Questions	Writing
Sky	Herrera	ML	Unsupervised Learning	Revising
Sky	Herrera	Python Careers	Intermediate Python Interview Questions	Revising
Victoria	Thompson	Python	Ranking Data in Python	Writing
Mike	McGill	NULL	NULL	NULL
NULL	NULL	Python	Pandas in Python	NULL
NULL	NULL	SQL	SQL Window Functions	NULL
NULL	NULL	SQL Careers	Basic SQL Interview Questions	NULL
NULL	NULL	SQL Careers	Intermediate SQL Interview Questions	NULL

No FULL JOIN, No SQL Mastery

FULL JOIN is probably not used as often as JOIN or LEFT JOIN. It’s a humble join that waits until you need it to shine brightly. But without it in your repertoire, you can’t call yourself an SQL master.

The above examples showed you practical scenarios when you’ll need to use all data from two (or more) tables. FULL JOIN makes it really easy!

Of course, you will need some more practice, as the most crucial part of using SQL joins is deciding which join to use. Once you master that, joins become easy – the syntax is the same, no matter which join type you use. Our SQL JOINs course can help you with mastering JOINs, as it brims with practical exercises. The same can be said of our SQL joins practice article with 12 examples.  After learning all that, even these SQL JOIN interview questions won’t be hard to answer.

Now, it’s up to you to use all these resources to master FULL JOIN!