In PostgreSQL (psql), JOINs are used to combine records from two or more tables based on a related column. Here’s a breakdown of different types of JOINs with examples.
1. INNER JOIN
Returns only matching records from both tables.
Example:
Consider two tables:
employees
| emp_id | name | dept_id |
|---|---|---|
| 1 | Elayaraj | 101 |
| 2 | Sugumar | 102 |
| 3 | Iyappan | 103 |
departments
| dept_id | dept_name |
|---|---|
| 101 | HR |
| 102 | IT |
| 104 | Marketing |
Query:
SELECT employees.emp_id, employees.name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;
Result:
| emp_id | name | dept_name |
|---|---|---|
| 1 | Elayaraj | HR |
| 2 | Sugumar | IT |
➡ Iyappan (dept_id 103) and Marketing (dept_id 104) are missing because there’s no match.
2. LEFT JOIN (LEFT OUTER JOIN)
Returns all records from the left table and matching records from the right table.
Query:
SELECT employees.emp_id, employees.name, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id;
Result:
| emp_id | name | dept_name |
|---|---|---|
| 1 | Elayaraj | HR |
| 2 | sugumar | IT |
| 3 | Iyappan | NULL |
➡ Iyappan is included, but with NULL for dept_name because dept_id 103 doesn't exist in departments.
3. RIGHT JOIN (RIGHT OUTER JOIN)
Returns all records from the right table and matching records from the left table.
Query:
SELECT employees.emp_id, employees.name, departments.dept_name
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.dept_id;
Result:
| emp_id | name | dept_name |
|---|---|---|
| 1 | Elayaraj | HR |
| 2 | sugumar | IT |
| NULL | NULL | Marketing |
➡ Marketing appears, but with NULL for emp_id and name because no employee belongs to dept_id 104.
4. FULL JOIN (FULL OUTER JOIN)
Returns all records from both tables, with NULLs where there is no match.
Query:
SELECT employees.emp_id, employees.name, departments.dept_name
FROM employees
FULL JOIN departments ON employees.dept_id = departments.dept_id;
Result:
| emp_id | name | dept_name |
|---|---|---|
| 1 | Elayaraj | HR |
| 2 | sugumar | IT |
| 3 | NULL | |
| NULL | NULL | Marketing |
➡ Charlie and Marketing both appear, even though they don’t have matches.
5. CROSS JOIN
Returns the Cartesian product of both tables (every combination of rows).
Query:
SELECT employees.name, departments.dept_name
FROM employees
CROSS JOIN departments;
Result:
| name | dept_name |
|---|---|
| elayaraj | HR |
| Elayaraj | IT |
| Elayaraj | Marketing |
| sugumar | HR |
| sugumar | IT |
| sugumar | Marketing |
| iyappan | HR |
| iyappan | IT |
| iyappan | Marketing |
➡ Every employee is paired with every department.
6. SELF JOIN
Joining a table with itself, useful for hierarchical data.
Example:
Consider an employees table with a manager_id column:
| emp_id | name | manager_id |
|---|---|---|
| 1 | Elayaraj | NULL |
| 2 | sugumar | 1 |
| 3 | iyappan | 1 |
| 4 | vimal | 2 |
Query:
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;
Result:
| employee | manager |
|---|---|
| Elayaraj | NULL |
| sugumar | Alice |
| Iyappan | Alice |
| vimal | Bob |
➡ Employees are matched with their managers.
Conclusion
| JOIN Type | Description |
|---|---|
| INNER JOIN | Returns only matching records. |
| LEFT JOIN | Returns all from the left table + matches from the right. |
| RIGHT JOIN | Returns all from the right table + matches from the left. |
| FULL JOIN | Returns all records from both tables. |
| CROSS JOIN | Returns the Cartesian product (all possible pairs). |
| SELF JOIN | Joins a table with itself (useful for hierarchical data). |
Top comments (0)