Types of Joins in SQL
Joins in SQL are used to combine rows from two or more tables based on related columns. There are several types of joins, each serving different purposes depending on how you want to retrieve and combine the data. Here are the main types of joins:
1. INNER JOIN
An INNER JOIN returns rows that have matching values in both tables. It combines rows from different tables where the join condition is met.
Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
Example
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;
2. LEFT (OUTER) JOIN
A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.
Syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
Example
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;
3. RIGHT (OUTER) JOIN
A RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the side of the left table.
Syntax
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;
Example
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;
4. FULL (OUTER) JOIN
A FULL JOIN (or FULL OUTER JOIN) returns all rows when there is a match in either the left table or the right table. It returns NULL for rows in the left table that do not have a match in the right table, and vice versa.
Syntax
SELECT columns
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;
Example
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.department_id;
5. CROSS JOIN
A CROSS JOIN returns the Cartesian product of the two tables, meaning it combines all rows from the first table with all rows from the second table.
Syntax
SELECT columns
FROM table1
CROSS JOIN table2;
Example
SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;
6. SELF JOIN
A SELF JOIN is a regular join, but the table is joined with itself. This can be useful for querying hierarchical data or comparing rows within the same table.
Syntax
SELECT a.column1, b.column2
FROM table a, table b
WHERE condition;
Example
SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
INNER JOIN employees e2
ON e1.manager_id = e2.employee_id;
Summary
- INNER JOIN: Returns only the matching rows between tables.
- LEFT JOIN: Returns all rows from the left table and matched rows from the right table.
- RIGHT JOIN: Returns all rows from the right table and matched rows from the left table.
- FULL JOIN: Returns all rows when there is a match in either table.
- CROSS JOIN: Returns the Cartesian product of both tables.
- SELF JOIN: Joins a table with itself.
Understanding these different types of joins is crucial for efficiently querying and combining data from multiple tables in SQL. Each join type serves a unique purpose and is chosen based on the specific requirements of the query.
