PSQL- JOIN Commands

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).

Mar 25, 2025 - 15:54

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).