FULL JOIN in SQL

FULL JOIN in SQL

Introduction A FULL JOIN in SQL combines the result sets of two tables based on a matching condition. It returns all the rows from both tables, including unmatched rows from either table. If there is no match between the tables, the unmatched rows will have NULL values for the columns of the other table.

Syntax The syntax for performing a FULL JOIN in SQL is as follows:

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

Example Let's consider two tables, "Customers" and "Orders," with the following structures and data:

sql
Customers
+----+----------+-------------+
| ID | Name     | City        |
+----+----------+-------------+
| 1  | John     | New York    |
| 2  | Emily    | London      |
| 3  | David    | Paris       |
+----+----------+-------------+

Orders
+------+--------+-------+
| ID   | Amount | Date  |
+------+--------+-------+
| 1    | 100    | 2022  |
| 2    | 200    | 2023  |
| 4    | 150    | 2023  |
+------+--------+-------+

To perform a FULL JOIN on the "Customers" and "Orders" tables based on the "ID" column, we can use the following SQL query:

sql
SELECT Customers.ID, Customers.Name, Orders.Amount, Orders.Date
FROM Customers
FULL JOIN Orders
ON Customers.ID = Orders.ID;

Explanation In the example above, we perform a FULL JOIN on the "Customers" and "Orders" tables based on the "ID" column. The result of the query will include all rows from both tables, combining the matching rows and including NULL values for the unmatched rows.

The result of the FULL JOIN query would be as follows:

sql
+------+------+--------+-------+
| ID   | Name | Amount | Date  |
+------+------+--------+-------+
| 1    | John | 100    | 2022  |
| 2    | Emily| 200    | 2023  |
| 3    | David| NULL   | NULL  |
| NULL | NULL | 150    | 2023  |
+------+------+--------+-------+

As seen in the result, the rows with ID 1 and 2 are matched and combined with their corresponding values from both tables. The row with ID 3 is from the "Customers" table but does not have a matching ID in the "Orders" table, resulting in NULL values for the "Amount" and "Date" columns. Similarly, the row with ID 4 is from the "Orders" table but does not have a matching ID in the "Customers" table, resulting in NULL values for the "Name" and "City" columns.

Conclusion A FULL JOIN in SQL allows you to combine rows from two tables, including both matching and non-matching rows. It can be useful when you want to retrieve all the data from both tables, regardless of any matching criteria.