SQL Joins
SQL joins are used to combine rows from two or more tables, based on a related column between them.
INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables.
Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;Example: INNER JOIN
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;This query selects employee names and their corresponding department names where there is a match between the department_id in the employees table and the id in the departments table.
LEFT JOIN
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
Syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;Example: LEFT JOIN
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;This query selects all employee names and their corresponding department names. If an employee does not belong to a department, the department name will be NULL.
RIGHT JOIN
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.
Syntax
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;Example: RIGHT JOIN
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;This query selects all department names and their corresponding employee names. If a department does not have any employees, the employee name will be NULL.
FULL JOIN
The FULL JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records. It returns NULL if there is no match.
Syntax
SELECT columns
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;Example: FULL JOIN
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.id;This query selects all employee names and department names. If an employee does not belong to a department, the department name will be NULL, and if a department does not have any employees, the employee name will be NULL.
CROSS JOIN
The CROSS JOIN keyword returns the Cartesian product of the two tables. This means it will return all possible combinations of rows from both tables.
Syntax
SELECT columns
FROM table1
CROSS JOIN table2;Example: CROSS JOIN
SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;This query returns a combination of all employee names and all department names.
SELF JOIN
The SELF JOIN is a regular join but the table is joined with itself.
Syntax
SELECT a.column_name, b.column_name
FROM table_name a, table_name b
WHERE condition;Example: SELF JOIN
SELECT a.name AS Employee, b.name AS Manager
FROM employees a, employees b
WHERE a.manager_id = b.id;This query returns employees and their corresponding managers from the same employees table.