Blog

Let’s delve into SQL and specifically focus on the FULL OUTER JOIN operation. SQL (Structured Query Language) is a powerful tool for managing and manipulating data within relational databases. The FULL OUTER JOIN operation allows you to combine rows from two or more tables based on a related column between them, including all rows from both tables regardless of whether there is a match.

1. Understanding the Concept:

Before we dive into coding, let’s understand the concept of FULL OUTER JOIN.

• A FULL OUTER JOIN returns all rows when there is a match in either left or right table.
• If there’s no match, NULL values are returned for the columns from the table that lacks a matching row.

2. Syntax:

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

3. Sample Tables:

For demonstration, let’s create two sample tables:

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(100),
    emp_department VARCHAR(100)
);

CREATE TABLE salaries (
    emp_id INT,
    salary DECIMAL(10, 2)
);

4. Populating Sample Data:

Let’s insert some data into these tables:

INSERT INTO employees (emp_id, emp_name, emp_department)
VALUES (1, 'John Doe', 'IT'),
       (2, 'Jane Smith', 'Finance'),
       (3, 'Mark Johnson', 'HR');

INSERT INTO salaries (emp_id, salary)
VALUES (1, 50000),
       (3, 60000);

5. Performing FULL OUTER JOIN:

Now, let’s perform a FULL OUTER JOIN to combine data from both tables:

SELECT employees.emp_id, emp_name, emp_department, salary
FROM employees
FULL OUTER JOIN salaries
ON employees.emp_id = salaries.emp_id;

6. Explanation:

• In the SELECT statement, we specify the columns we want to retrieve from both tables.
• In the FROM clause, we list the tables we want to join (employees and salaries).
• In the ON clause, we specify the condition for joining the tables, in this case, matching emp_id.

7. Output:

The output of the FULL OUTER JOIN will include all rows from both tables, combining information where there’s a match and inserting NULL values where there isn’t:

emp_id	emp_name	emp_department	salary
1	John Doe	IT	50000.00
2	Jane Smith	Finance	NULL
3	Mark Johnson	HR	60000.00

Conclusion

That’s it! You’ve learned how to use FULL OUTER JOIN in SQL to combine data from multiple tables, ensuring all rows are included regardless of matching criteria. This can be particularly useful when you need to compare datasets or analyze data from different sources.