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
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
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
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_name column values from the
Department table and for non-matching rows, it will show NULL.
emp_id = 5, there is no department defined by keeping
dept_id = null, so the above query shows NULL value in
From the resultset, we can select any column from the
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_name from the
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);