SQL Join FULL JOIN

The SQL FULL JOIN clause is used to combine rows from two or more tables based on a related column between them. It returns all the rows from both tables and matches them where possible.

Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

Example

Let's say we have two tables: employees and departments. The employees table contains information about employees including their ID, name, job title, and department ID. The departments table contains information about the departments including their ID and name.

Now, if we want to see all employees along with their department name, we can use the following SQL FULL JOIN query:

SELECT employees.name, departments.name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id;

Output

The output of the above query will be a table containing all employee names along with their department names. If a department does not have any employees, it will still be included in the output with null values for employee names.

Name	Name
John	Sales
Sarah	Marketing
Michael	Finance
null	IT
Jennifer	null

Explanation

In the above query, we use the SQL FULL OUTER JOIN clause to get all the rows from both the employees and departments tables and match them where possible based on the department_id column.

Use

The SQL FULL JOIN clause is useful in situations where you want to combine data from multiple tables and include all the rows from both tables.

Important Points

• In the SQL FULL JOIN clause, all rows from both tables are returned, even if there is no match found between them.
• If there is no match found between the tables, null values are returned for the columns in the result set.

Summary

• SQL FULL JOIN clause is used to combine data from two or more tables based on a related column between them.
• It returns all the rows from both tables, even if there is no match found between them.
• If there is no match found, null values are returned for the columns in the result set.
• It is useful in situations where you want to combine data from multiple tables and include all the rows from both tables.