Joins

Self Join :

Joins a table with itself to compare rows within the same table.

Example: Find employees and their managers (same table)

SELECT e1.name AS Employee, e2.name AS Manager

FROM employees e1

LEFT JOIN employees e2

ON e1.manager_id = e2.employee_id;

INNER JOIN (Default JOIN):

• Returns only the matching rows from both tables based on the condition.

Example: Fetch employees with their department names

SELECT e.employee_id, e.name, d.department_name

FROM employees e

INNER JOIN departments d

ON e.department_id = d.department_id;

 LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table.

If no match is found in the right table, it returns NULL.

Example: Fetch all employees, even if they have no department

SELECT e.employee_id, e.name, d.department_name

FROM employees e

LEFT JOIN departments d

ON e.department_id = d.department_id;

Note : Employees without a department will show NULL in the department column.

 

 

 

 

 

 

 

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table and matching rows from the left table.

If no match is found in the left table, it returns NULL.

Example: Fetch all departments, even if they have no employees

SELECT e.employee_id, e.name, d.department_name

FROM employees e

RIGHT JOIN departments d

ON e.department_id = d.department_id;

Departments without employees will still be displayed with NULL for employees.

 

FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables.

If there’s no match, it returns NULL for the missing side.

Example: Fetch all employees and all departments, even if no matching record exists

SELECT e.employee_id, e.name, d.department_name

FROM employees e

FULL JOIN departments d

ON e.department_id = d.department_id;

Employees without departments and departments without employees will appear with NULL values.

CROSS JOIN

Returns Cartesian Product (every row from the first table joins with every row from the second table).

SELECT e.name, d.department_name

FROM employees e

CROSS JOIN departments d;

If employees table  has 5 rows and departments has 3 rows, the result will have 5 × 3 = 15 rows.

Real Life Example : Combining every shirt with every pant

 

 

 

 

NATURAL JOIN

• Joins two tables based on columns with the same name and data type.
• No need to specify the ON condition.

Example: Fetch employees and their departments automatically matching common columns

SELECT e.employee_id, e.name, d.department_name

FROM employees e

NATURAL JOIN departments d;

Works only if both tables have a column with the same name, like department_id.