In SQL, the JOIN clause is used to combine rows from two or more tables based on a related column between them.
Types of JOINs
1. INNER JOIN
An **INNER JOIN returns only the rows that have matching values in both tables.
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.id EXPLANATIONS
**This query selects all employees and their corresponding department names where there is a match between employees.department_id and departments.id.
2. LEFT JOIN (or LEFT OUTER JOIN)
**A LEFT JOIN returns all rows from the left table (table1), and the matched rows from the right table (table2). 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.common_column = table2.common_column; _EXAMPLE_
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id; EXPLANATIONS
**This query selects all employees, including those who do not belong to any department. For employees without a department, department_name will be NULL.
3.RIGHT JOIN (or RIGHT OUTER JOIN)
**A RIGHT JOIN returns all rows from the right table (table2), and the matched rows from the left table (table1). 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.common_column = table2.common_column; EXAMPLE
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id; _EXPLANATIONS_
**This query selects all departments, including those without any employees. For departments without employees, name will be NULL.
4.FULL JOIN (or FULL OUTER JOIN)
**A FULL JOIN returns all rows when there is a match in either left (table1) or right (table2) table. Rows without a match in one of the tables will have NULLs for columns from that table.
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.id; _EXPLANATIONS_
This query selects all employees and departments, showing NULLs where there is no match.
5.CROSS JOIN
**A CROSS JOIN returns the Cartesian product of the two tables, i.e., it returns all possible combinations of rows from the tables.
_SYNTAX_
SELECT columns
FROM table1
CROSS JOIN table2; _EXAMPLE_
SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments; EXPLANATIONS
**This query combines each employee with each department, resulting in a large number of rows.
6.SELF JOIN
**A SELF JOIN is a regular join, but the table is joined with itself.
SYNTAX
SELECT a.columns, b.columns
FROM table a, table b
WHERE a.common_column = b.common_column; EXAMPLE
SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
INNER JOIN employees e2
ON e1.manager_id = e2.id; Key Points:
i. JOIN Conditions:The ON clause specifies the condition for the join, typically matching a column from one table with a column from another table.
ii. Aliases:Using table aliases (e.g., e1, e2 in the SELF JOIN example) can make the query more readable and manageable, especially for self joins or when the same table is used multiple times.
iii. NULL Handling:In OUTER JOINs (LEFT, RIGHT, FULL), unmatched rows will have NULL values for the columns of the table that does not have the match.