Different Types of Joins in SQL

In SQL, JOINs are used to combine rows from two or more tables based on a related column between them. There are different types of joins in SQL that allow you to fetch data in various ways depending on how you want to combine the tables. These include:

INNER JOIN
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN (or RIGHT OUTER JOIN)
FULL JOIN (or FULL OUTER JOIN)
CROSS JOIN
SELF JOIN

1. INNER JOIN

Definition:

The INNER JOIN returns only the rows where there is a match in both tables. If there is no match between the columns being joined, those rows will not appear in the result.

Syntax:

SELECT columns FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Example:

Consider two tables:

employees: Contains employee details.
departments: Contains department details.

To find the employees and their corresponding 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;
Explanation:

The INNER JOIN ensures that only employees who belong to a department are returned, excluding employees with no department assigned.

Result:
The query will return all employees who are assigned to a department.

2. LEFT JOIN (or LEFT OUTER JOIN)

Definition:

The LEFT JOIN returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.

Syntax:

SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Example:

To find all employees and their departments (including those without a department):

SELECT e.employee_id, e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;
Explanation:

The LEFT JOIN includes all employees, even those who do not belong to any department. For those employees, the department_name will be NULL.

Result:
The query will return all employees, with NULL in the department_name column for employees who do not have a department.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Definition:

The RIGHT JOIN is similar to the LEFT JOIN, but it returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.

Syntax:

SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Example:

To find all departments and their employees (including departments with 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;
Explanation:

The RIGHT JOIN ensures that all departments are returned, even if there are no employees assigned to them. For departments without employees, the employee_id and employee_name will be NULL.

Result:
The query will return all departments, with NULL in the employee details for departments with no employees.

4. FULL JOIN (or FULL OUTER JOIN)

Definition:

The FULL JOIN (or FULL OUTER JOIN) returns all rows from both tables. If there is a match, the rows are combined. If there is no match, NULL values are returned for columns from the table without a match.

Syntax:

SELECT columns FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;

Example:

To find all employees and all departments, including those without a matching department or employee:

SELECT e.employee_id, e.name, d.department_name FROM employees e FULL JOIN departments d ON e.department_id = d.department_id;
Explanation:

The FULL JOIN returns all rows from both the employees and departments tables. Employees without a department and departments without employees will have NULL values in the columns of the other table.

Result:
The query will return all employees and all departments, filling NULL where no match exists.

5. CROSS JOIN

Definition:

The CROSS JOIN returns the Cartesian product of the two tables, i.e., it returns every combination of rows between the two tables. This join does not require a condition to match rows.

Syntax:

SELECT columns FROM table1 CROSS JOIN table2;

Example:

To get every combination of employee and department:

SELECT e.employee_id, e.name, d.department_name FROM employees e CROSS JOIN departments d;
Explanation:

The CROSS JOIN will return every possible combination of employee and department, even if an employee is not assigned to a department.

Result:
If there are 10 employees and 5 departments, the query will return 50 rows (10 × 5), representing all combinations of employees and departments.

6. SELF JOIN

Definition:

A self join is a join where a table is joined with itself. It is useful when you need to compare rows within the same table.

Syntax:

SELECT columns FROM table1 t1, table2 t2 WHERE t1.column_name = t2.column_name;

Example:

Consider a employees table where each employee has a manager_id pointing to their manager's employee_id. To find each employee and their manager's name:

SELECT e1.employee_id, e1.name AS employee_name, e2.name AS manager_name FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;
Explanation:

The employees table is aliased as e1 for employees and e2 for managers. The LEFT JOIN ensures that even employees without managers are included.

Result:
The query will return a list of employees along with their manager's name (or NULL if they don’t have a manager).

Summary of SQL Joins

Join Type	Description	Includes Rows
INNER JOIN	Returns rows where there is a match in both tables.	Only matching rows from both tables.
LEFT JOIN	Returns all rows from the left table and matched rows from the right table.	All rows from the left table and matched rows from the right.
RIGHT JOIN	Returns all rows from the right table and matched rows from the left table.	All rows from the right table and matched rows from the left.
FULL JOIN	Returns all rows when there is a match in either left or right table.	All rows from both tables, with NULL where no match exists.
CROSS JOIN	Returns the Cartesian product of both tables (every combination of rows).	Every combination of rows from both tables.
SELF JOIN	Joins a table with itself.	A table joined with itself, typically used for hierarchical data.

Unlocking the World of Business Intelligence with SQLBI