In SQL, a `FULL JOIN` (or `FULL OUTER JOIN`) is a type of join operation that combines the rows from two tables, including all the matched and unmatched rows from both tables. It returns a result set that includes all the rows from both tables, and if there is no match, NULL values are returned for the columns from the non-matching table.
The basic syntax for a `FULL JOIN` is as follows:
sql
SELECT column_list
FROM table1
FULL JOIN table2 ON join_condition;
Here's a breakdown of the components:
- `column_list`: The list of columns you want to select from the resulting joined table.
- `table1`: The first table in the join operation.
- `table2`: The second table in the join operation.
- `join_condition`: The condition that determines how the two tables are related.
To illustrate this further, let's consider two tables: `employees` and `departments`, with the following structures:
employees
-----------------------------
| emp_id | emp_name | dept_id |
-----------------------------
| 1 | John | 101 |
| 2 | Jane | 102 |
| 3 | Alice | 103 |
| 4 | Bob | 104 |
-----------------------------
departments
---------------------
| dept_id | dept_name |
---------------------
| 101 | Sales |
| 103 | Finance |
| 105 | IT |
---------------------
Suppose you want to retrieve all employees and their corresponding department names, including those employees and departments without matches. You can use a `FULL JOIN` as follows:
sql
SELECT employees.emp_id, employees.emp_name, departments.dept_name
FROM employees
FULL JOIN departments ON employees.dept_id = departments.dept_id;
The result of this query would be:
---------------------------------------------------
| emp_id | emp_name | dept_name |
---------------------------------------------------
| 1 | John | Sales |
| 2 | Jane | NULL (No matching department)|
| 3 | Alice | Finance |
| 4 | Bob | NULL (No matching department)|
| NULL | NULL | IT |
---------------------------------------------------
In this example, the `FULL JOIN` returns all rows from both the `employees` and `departments` tables, including both matched and unmatched rows. If a row doesn't have a match in the other table, NULL values are displayed for the columns from the non-matching table.
Please note that not all database systems support the `FULL JOIN` syntax. In such cases, you can achieve a similar result by combining a `LEFT JOIN` and a `RIGHT JOIN` using the `UNION` or `UNION ALL` operator.
Silan Software is one of the India's leading provider of offline & online training for Java, Python, AI (Machine Learning, Deep Learning), Data Science, Software Development & many more emerging Technologies.
We provide Academic Training || Industrial Training || Corporate Training || Internship || Java || Python || AI using Python || Data Science etc