SQL FULL JOIN

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. We learn about SQL FULL JOIN by understanding various examples in detail.

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.

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.

1. Books Table

This table Represents the list of books in the system

2. Authors Table

This table represents the authors who have written the books.

3. Publishers Table

This table represents the authors who have published the books.

Example 1: Joining Multiple Tables with Full Join

Query:

SELECT

b.BOOK_ID,

b.BOOK_NAME,

a.AUTHOR_NAME,

p.PUBLISHER_NAME

FROM

Books b ,Author a, Publisher p

FULL JOIN Authors a ON b.BOOK_ID = a.AUTHOR_ID

FULL JOIN Publishers p ON b.BOOK_ID = p.PUBLISHER_ID;

Output:

Explanation:

• The Books table uses BOOK_ID as the primary key.
• The Authors table uses AUTHOR_ID, which matches the BOOK_ID.
• The Publishers table uses PUBLISHER_ID, which also matches the BOOK_ID.

So the relationship is:

• Each BOOK_ID corresponds to one AUTHOR_ID and one PUBLISHER_ID.

• FULL JOIN Authors a ON b.BOOK_ID = a.AUTHOR_ID
  Combines all books with authors where IDs match, and retains unmatched books or authors (none in this case).

• FULL JOIN Publishers p ON b.BOOK_ID = p.PUBLISHER_ID
  Then joins in publishers using the same BOOK_ID.

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, Author a, Publisher p
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:

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.