Full Join

In SQL, a FULL JOIN is a type of join operation that combines the rows from two tables, including both matching and non-matching rows. It is also known as a full outer join.

To perform a FULL JOIN, you need to specify two tables and a join condition that determines how the two tables should be joined. The join condition is typically based on a common column between the two tables.

Syntax

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

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

In the above syntax, the SELECT statement specifies the columns that you want to retrieve from the combined result set. The FROM clause specifies the first table (table1), and the FULL OUTER JOIN keyword is used to indicate that a full join should be performed. The second table (table2) is specified after the FULL OUTER JOIN keyword. The ON keyword is used to specify the join condition.

The result of a FULL JOIN includes all the rows from both tables, including those that do not have a match in the other table. If a row from one table does not have a match in the other table, the columns for the non-matching table will contain NULL values.

Example

Here is an example that demonstrates a FULL JOIN in SQL:

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

In this example, we are joining the employees table with the departments table using the department_id column as the join condition. The result set will include all rows from both tables, regardless of whether they have a match in the other table.

FULL JOINs can be useful when you want to combine data from two tables and ensure that you include all the data from both tables, even if there are no matches between them. However, it can also lead to large result sets if the tables being joined have a lot of data.