PostgreSQL Joins

1. INNER JOIN

An INNER JOIN returns rows when there is at least one match in both tables. If there is no match, the row will not appear in the result set.

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

2. LEFT JOIN (or LEFT OUTER JOIN)

A LEFT JOIN returns all the rows from the left table and the matched rows from the right table. If there is no match, NULL values will be returned for columns from the right table.

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

3. RIGHT JOIN (or RIGHT OUTER JOIN)

A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values will be returned for columns from the left table.

SELECT employees.nae, departments.name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

4. FULL JOIN (or FULL OUTER JOIN)

A FULL JOIN returns rows when there is a match in one of the tables. It returns NULL for the non-matching rows in both tables.

SELECT employees.name, departments.name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.id;