close
The Wayback Machine - https://web.archive.org/web/20250226101421/https://www.geeksforgeeks.org/sql-full-join/
Open In App

SQL FULL JOIN

Last Updated : 17 Dec, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

In SQL, the FULL JOIN (or FULL OUTER JOIN) is a powerful technique used to combine records from two or more tables. Unlike an INNER JOIN, which only returns rows where there are matches in both tables, a FULL JOIN retrieves all rows from both tables, filling in NULL values where matches do not exist. In this article, We will learn about SQL FULL JOIN by understanding various examples in detail.

In this article, we will delve into the concept of SQL FULL JOIN, understand its syntax, and explore practical examples to help you master its use in SQL queries.

SQL FULL JOIN

  • The FULL JOIN or FULL OUTER JOIN in SQL is used to retrieve all rows from both tables involved in the join, regardless of whether there is a match between the rows in the two tables.
  • It combines the results of both a LEFT JOIN and a RIGHT JOIN.
  • When there is no match, the result will include NULLs for the columns of the table that do not have a matching row.

Tip: We can use FULL JOIN to combine multiple tables, by sequentially performing FULL JOIN on two tables at a time.

Syntax:

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

Explanation:

  • SELECT columns: Specifies the columns to retrieve.
  • FROM table1: The first table to be joined.
  • FULL JOIN table2: Specifies the second table to join with the first table using a FULL JOIN.
  • ON table1.column = table2.column: Defines the condition to match rows between the two tables.

This query retrieves all records from both table1 and table2, returning NULL where there are no matches.

Examples of SQL FULL JOIN

Let's look at some examples of the FULL JOIN in SQL and understand it's working.

First, let's create a demo database and two tables on which we will perform the JOIN.

Table 1- Students

IDNAMEBRANCHNUMBER
1SURYANSH JOHARICS984012
2AMAN SHARMAIT771346
3DEV VERMAME638587
4JOY SMITHCE876691
5CHARLES GATTOEE997679

Table 2- Library

BOOK_IDBOOK_NAMEISSUED_ONDUE_DATE
1RD SHARMA2023-01-012023-01-08
2GATE CRACKER2023-02-022023-02-09
3MORRIS MANO2023-03-032023-03-10
4NK PUBLICATIONS2023-04-042023-04-11
5BIG BANG THEORY2023-05-052023-05-12

Example 1: Joining Multiple Tables with Full Join

We want to demonstrate how to use FULL JOIN to combine two or more tables based on common columns. In this case, we will create two additional tables: Authors and Publishers, and join them with the Books table using a FULL JOIN.

Query:

SELECT 
b.BOOK_ID,
b.BOOK_NAME,
a.AUTHOR_NAME,
p.PUBLISHER_NAME
FROM
Books b
FULL JOIN Authors a ON b.BOOK_ID = a.AUTHOR_ID
FULL JOIN Publishers p ON b.BOOK_ID = p.PUBLISHER_ID;

Output:

BOOK_IDBOOK_NAMEAUTHOR_NAMEPUBLISHER_NAME
1RD SHARMARam KumarPearson
2GATE CRACKERShyam SunderWiley
3MORRIS MANOSita SinghMcGraw-Hill
4NK PUBLICATIONSMohan GuptaSpringer
5BIG BANG THEORYRaj KapoorElsevier

Explanation:

In this query, we used FULL JOIN to join three tables: Books, Authors, and Publishers. The FULL JOIN ensures that all records from both tables are included, whether or not they match. Here, each book has a corresponding author and publisher. If any book didn't have an author or publisher, the result would still include that row with NULL in the respective columns.

Example 2: Full Join with WHERE Clause

Now, we want to filter the results from the above join based on a specific condition. We will select only books that have "Sharma" in the book name.

Query:

SELECT 
b.BOOK_ID,
b.BOOK_NAME,
a.AUTHOR_NAME,
p.PUBLISHER_NAME
FROM
Books b
FULL JOIN Authors a ON b.BOOK_ID = a.AUTHOR_ID
FULL JOIN Publishers p ON b.BOOK_ID = p.PUBLISHER_ID
WHERE
b.BOOK_NAME LIKE '%Sharma%';

Output:

BOOK_IDBOOK_NAMEAUTHOR_NAMEPUBLISHER_NAME
1RD SHARMARam KumarPearson

Explanation:

In this example, the WHERE clause filters out all books that do not contain the word "Sharma" in their name. After applying the filter, only the record for "RD SHARMA" remains.

Conclusion

The FULL JOIN in SQL is an essential tool when you need to combine data from multiple tables while ensuring that no rows are omitted, even if there are no matches between the tables. It allows you to retrieve complete datasets, with NULL values indicating missing data from either side. Adding WHERE clauses allows for further refinement of the results, providing a flexible mechanism for managing data from multiple sources.

FAQs

What is a full join in SQL?

A FULL JOIN returns all rows from both tables, whether or not there is a match between them. Unmatched rows are filled with NULLs where data is missing from one of the tables.

How does a left join differ from a full join in SQL?

A LEFT JOIN returns all rows from the left table and matching rows from the right table, while a FULL JOIN returns all rows from both tables, with NULLs where there is no match on either side.

What is the distinction between an outer join and a full outer join in SQL?

A FULL OUTER JOIN is a type of OUTER JOIN that returns all rows from both tables. It’s a specific version of an outer join that includes unmatched rows from both sides.


Next Article
Article Tags :

Similar Reads

three90RightbarBannerImg