PostgreSQL: Left Outer Join

The LEFT JOIN is a type of join where it returns all records from the left table and matching records from the right table.

Here the left table is the table that comes to the left side of the "LEFT JOIN" phrase in the query, and the right table refers to a table that comes at the right side or after the "LEFT JOIN" phrase. It returns NULL for all non-matching records from the right table. In some databases, it is called LEFT OUTER JOIN.

Here is a diagram that represents LEFT JOIN.

SELECT <table_1.column_name(s)>, <table_2.column_name(s)>
FROM <table_1>
LEFT [OUTER] JOIN <table_2>
ON <table_1.column_name> = <table_2.column_name>;

As per the above syntax, we have table_1 as the left table and table_2 as the right table and they have some matching columns between them.

For the demo purpose, we will use the following Employee and Department tables.

Notice Department table is parent table with dept_id as a primary key. Employee table is child table that has dept_id as foreign key referencing dept_id column of Department table. Now let's join both the first table (Employee) with a second table (Department) by matching values of the dept_id column.

The Employee table has some employee, for e.g. emp_id = 5 who does not have dept id assigned to it and for department with dept_id = 4, there is no employee. Let's use LEFT JOIN to select data from an Employee that may or may not have matching data in the Department table.

SELECT emp.emp_id, emp.first_name, emp.last_name, dept.dept_id, dept.dept_name 
FROM Employee emp LEFT JOIN department dept
ON emp.dept_id = dept.dept_id;

Here, the LEFT OUTER JOIN selects all rows of a left table that is the Employee. It may or may not have matching data in the right table, that is Department. For matching data, it will show dept_id and dept_name column values from the Department table and for non-matching rows, it will show NULL.

For emp_id = 5, there is no department defined by keeping dept_id = null, so the above query shows NULL value in dept_id and dept_name columns.

From the resultset, we can select any column from the Employee or Department table in the SELECT clause. As you can see, in the above query we selected emp_id, first_name and last_name from the Employee table and dept_id and dept_name from the Department table.

If Employee and Department both tables have the same column name used in the ON clause, like in our case dept_id, then you can use the USING syntax like this.

SELECT emp.emp_id, emp.first_name, emp.last_name, dept.dept_id, dept.dept_name 
FROM Employee emp LEFT JOIN department dept
USING (dept_id);