SQL OUTER JOIN

SQL OUTER JOIN is used to combine records from two or more tables in a database. OUTER JOINS are of three types - LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. The OUTER JOIN includes the results of the INNER JOIN as well as those records from one side or both sides that do not have a match in the other side.

Syntax

To perform an OUTER JOIN in SQL, you need to use the JOIN keyword followed by the name of the second table, and then the ON keyword followed by the condition based on which the tables are joined. The syntax for different types of OUTER JOIN are as follows:

• LEFT OUTER JOIN: This join is used to return all the records from the left table, and matching records from the right table. If there is no match on the right table, it returns NULL values for the right side.

    SELECT * 
    FROM table1 
    LEFT OUTER JOIN table2 
    ON table1.column = table2.column;
  

• RIGHT OUTER JOIN: This join is used to return all the records from the right table, and matching records from the left table. If there is no match on the left table, it returns NULL values for the left side.

    SELECT * 
    FROM table1 
    RIGHT OUTER JOIN table2 
    ON table1.column = table2.column;
  

• FULL OUTER JOIN: This join returns all the records from both tables where the JOIN condition is satisfied. It returns NULL values for those records that do not have a match on the either side.

    SELECT * 
    FROM table1 
    FULL OUTER JOIN table2 
    ON table1.column = table2.column;
  

Example

Consider two tables, employees and departments, with the following data:

employees table:

emp_id	last_name	department_id
1	Johnson	4
2	Smith	3
3	Williams	3
4	Jones	2
5	Brown	4

departments table:

dept_id	name
1	Marketing
2	Finance
3	Operations
4	Human Resources

The following SQL query joins the employees and departments tables using LEFT OUTER JOIN:

SELECT employees.emp_id, employees.last_name, departments.name 
FROM employees 
LEFT OUTER JOIN departments 
ON employees.department_id = departments.dept_id;

This will give the following output:

emp_id	last_name	name
1	Johnson	Human Resources
2	Smith	Operations
3	Williams	Operations
4	Jones	Finance
5	Brown	Human Resources
NULL	NULL	Marketing

Explanation

The above query uses LEFT OUTER JOIN to join the employees table on the left and departments table on the right. The ON clause specifies that the department_id column from the employees table should match with the dept_id column of the departments table.

The output includes all records from the employees table along with matching records from the departments table. In this case, all records except one from the employees table have a match in the departments table. The record for the Marketing department does not have a matching record in the employees table, so a NULL value is displayed for both employee ID and last name for this record.

Use

The OUTER JOIN is useful in situations where we need to combine data from two or more tables, and also include unmatched records from one or both tables. Some common use cases for OUTER JOIN include:

• To find records in one table that do not exist in another table.
• To merge data from two tables while keeping the unmatched records.
• To summarize data from multiple tables where some records may not have a match on one or more tables.

Important Points

• OUTER JOIN includes all records from both tables where the JOIN condition is met, and includes NULL values for non-matching records.
• OUTER JOIN is of three types, namely LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
• LEFT OUTER JOIN all records from the left table, and matching records from the table.
• RIGHT JOIN returns all records from the right, and matching records from the left table.
• FULL OUTER returns all matched and non-matched records from both tables.

Summary

In summary, OUTER JOIN is a powerful SQL feature that you to combine records from two or more tables, and also unmatched records. The choice of using LEFT OUTER JOIN, RIGHT JOIN, or FULL OUTER JOIN depends on the use case and output.