SQL FULL JOIN

What is FULL JOIN?

FULL JOIN, also known as FULL OUTER JOIN, is a type of join that returns all records from both the left table (table1) and the right table (table2). If there are records in one table that do not have a corresponding record in the other table, the result set will include NULL values for the missing matches.

Syntax of FULL JOIN

SELECT column1, column2, ...
FROM table1
FULL JOIN table2 ON table1.column_name = table2.column_name;

In this syntax, table1 and table2 are the tables to join, and the join is performed based on the specified column_name.

FULL JOIN Example

Here’s an example of using FULL JOIN to retrieve data from two related tables:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

This query selects the CustomerName from the Customers table and the OrderID from the Orders table. If a customer does not have any orders, the OrderID will be NULL, and if an order does not have a matching customer, the CustomerName will be NULL.

Use Cases of FULL JOIN

FULL JOIN is useful in various scenarios, such as:

• Retrieving all records from both tables while showing related data when available.
• Finding all entries in either table regardless of whether they have matching records in the other table.
• Generating reports that require comprehensive data from multiple sources.

Practice Exercise

Write your own FULL JOIN queries using example tables like Customers and Orders. Try to include different columns in your SELECT statement!