If you are writing a query for your business, you might have come across confusion in the use of different types of JOINs in PostgreSQL. In this blog, we will discuss the different types of JOINs available in postgres.
A JOIN allows us to retrieve related data from two or more tables based on a condition between them. We have four commonly used JOINs in PostgreSQL:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
What Is a JOIN in PostgreSQL?
A JOIN combines rows from two or more tables based on a related column.
Consider two demo tables:
Table employees
| employee_id | employee_name | department_id |
| 101 | Alice | 1 |
| 102 | Bob | 1 |
| 103 | Charlie | 2 |
| 104 | David | 3 |
| 105 | Eva | NULL |
| 106 | Frank | 5 |
Table departments
| department_id | | department_name
|
| 1 | Development |
| 2 | HR |
| 3 | Finance |
| 4 | Marketing |
The department_id column is the column between these tables. For example:
- Alice belongs to department 1.
- Then, department 1 is development.
- Thus, Alice’s department is development.
A join allows postgres to combine these related rows.
Now, let’s go with an example: create demo tables:
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50));
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
department_id INT
);
--Insert data into the tables:
INSERT INTO departments (department_id, department_name)
VALUES
(1, 'Development'),
(2, 'HR'),
(3, 'Finance'),
(4, 'Marketing');
INSERT INTO employees (employee_id, employee_name, department_id)
VALUES
(101, 'Alice', 1),
(102, 'Bob', 1),
(103, 'Charlie', 2),
(104, 'David', 3),
(105, 'Eva', NULL),
(106, 'Frank', 5);
INNER JOIN:
An INNER JOIN will only return those rows that have matching values in both tables.
Syntax looks like:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
For example, suppose we need to show each employee along with their department name.
SELECT
e.employee_id,
e.employee_name,
d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
The result would look like :
employee_id | employee_name | department_name
-------------+---------------+-----------------
101 | Alice | Development
102 | Bob | Development
103 | Charlie | HR
104 | David | Finance
(4 rows)
Here, we have four employees in the result because Eva has NULL in the department table, and Frank has department ID 5 in the employees table, which is not available in the department table. So INNER JOIN is useful when you only want records that have a valid relationship in both tables.
LEFT JOIN
A left join returns every row from the left table, the matching rows from the right table and NULL values when there is no match. That is, the result will contain rows from the left table and whatever matches the right table.
Syntax is like:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
For example, let’s fetch all employees and their department names.
SELECT
e.employee_id,
e.employee_name,
d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;
The result:
employee_id | employee_name | department_name
-------------+---------------+-----------------
101 | Alice | Development
102 | Bob | Development
103 | Charlie | HR
104 | David | Finance
105 | Eva |
106 | Frank |
(6 rows)
Even though Eva and Frank don't have matching departments, they are still included because employees are in the left table. In the case of a left join, for unmatched rows postgres fills those with NULL values.
RIGHT JOIN:
A right join has an opposite perspective of a left join; that is, it returns every row from the right table and matches rows from the left table. Gives NULL values when there is no match.
Syntax:
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
For example, to find all departments and the employees belonging to them.
SELECT
e.employee_name,
d.department_id,
d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;
The result is like:
employee_name | department_id | department_name
---------------+---------------+-----------------
Alice | 1 | Development
Bob | 1 | Development
Charlie | 2 | HR
David | 3 | Finance
| 4 | Marketing
(5 rows)
Note that marketing is included even though it does not have any employees. Because departments is the right table, and RIGHT JOIN will keep every row from the right table. And since there are no matching rows, those portions will be NULL; RIGHT JOIN can be useful when the table on the right represents the records you want to preserve.
FULL JOIN:
A FULL JOIN or FULL OUTER JOIN returns all rows from both tables. It includes matching rows, unmatched rows from the left table, and unmatched rows from the right table.
Syntax:
SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;
Example:
SELECT
e.employee_id,
e.employee_name,
d.department_id,
d.department_name
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id;
And the result is like:
employee_id | employee_name | department_id | department_name
-------------+---------------+---------------+-----------------
101 | Alice | 1 | Development
102 | Bob | 1 | Development
103 | Charlie | 2 | HR
104 | David | 3 | Finance
105 | Eva | |
106 | Frank | |
| | 4 | Marketing
(7 rows)
Here, even though Eva and Frank do not have a matching department, they have appeared in the result, but their department columns are NULL.
The best way to learn JOINs is to experiment with the queries yourself, change records in the tables, add unmatched records, and observe how the result changes in a possible demo or test database. This makes the difference between INNER, LEFT, RIGHT, and FULL JOIN much easier to understand than just simply memorizing their definitions.