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

departments

Query:

SELECT employees.emp_id, employees.name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;

Result:

➡ 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:

➡ 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:

➡ 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:

➡ 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:

➡ 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:

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:

➡ Employees are matched with their managers.

Conclusion