SQL FULL JOIN

SQL FULL JOIN Statement

The SQL FULL JOIN statement is used to return all records when there is a match in either left or right table records. If there is no match, the result is NULL from the non-matching table.

Syntax

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

The FULL JOIN statement has the following components:

  • column1, column2, ...: The columns to be retrieved.
  • table1: The name of the first table.
  • table2: The name of the second table.
  • table1.column = table2.column: The condition to join the tables.

Example SQL FULL JOIN Statement Queries

Let's look at some examples of SQL FULL JOIN statement queries:

1. Basic FULL JOIN Example

SELECT employees_1.id, employees_1.first_name, employees_2.department_name
FROM employees_1
FULL JOIN employees_2
ON employees_1.id = employees_2.id;

This query retrieves the id and first_name columns from the employees_1 table and the department_name column from the employees_2 table. It returns all records from both tables where there is a match on the id column, and if there is no match, the result is NULL from the non-matching table.

2. FULL JOIN with WHERE Clause

SELECT employees_1.id, employees_1.first_name, employees_2.department_name
FROM employees_1
FULL JOIN employees_2
ON employees_1.id = employees_2.id
WHERE employees_2.department_name IS NOT NULL;

This query retrieves the id and first_name columns from the employees_1 table and the department_name column from the employees_2 table. It returns all records from both tables where there is a match on the id column, and filters the results to include only those where the department_name is not null.

Full Example

Let's go through a complete example that includes creating tables, inserting data, and querying the tables with the FULL JOIN statement.

Step 1: Creating Tables

This step involves creating new tables named employees_1 and employees_2 to store employee data.

CREATE TABLE employees_1 (
    id INT PRIMARY KEY,
    first_name VARCHAR(50)
);

CREATE TABLE employees_2 (
    id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

In this example, we create two tables: employees_1 with columns for id and first_name, and employees_2 with columns for id and department_name.

Step 2: Inserting Data into the Tables

This step involves inserting some sample data into the employees_1 and employees_2 tables.

INSERT INTO employees_1 (id, first_name)
VALUES (1, 'John'), (2, 'Jane'), (3, 'Jim');

INSERT INTO employees_2 (id, department_name)
VALUES (1, 'Engineering'), (2, 'HR'), (4, 'Marketing');

Here, we insert data into the employees_1 and employees_2 tables.

Step 3: Querying the Tables with FULL JOIN

This step involves selecting the data from the employees_1 and employees_2 tables and joining them using the FULL JOIN statement.

SELECT employees_1.id, employees_1.first_name, employees_2.department_name
FROM employees_1
FULL JOIN employees_2
ON employees_1.id = employees_2.id;

This query retrieves all the rows from both the employees_1 and employees_2 tables, matching them on the id column. The result will be:

id  first_name  department_name
--- ----------- ---------------
1   John        Engineering
2   Jane        HR
3   Jim         NULL
4   NULL        Marketing

Conclusion

The SQL FULL JOIN statement is a powerful tool for returning all records when there is a match in either left or right table records. Understanding how to use the FULL JOIN statement and its syntax is essential for effective data retrieval and analysis in SQL databases.