Join Tables
In SQL, a JOIN operation is used to combine rows from two or more tables based on a related column between them. Joins are essential for retrieving data from multiple tables in a relational database, allowing you to perform complex queries across different datasets.
1. INNER JOIN:
An INNER JOIN returns only the rows that have matching values in both tables. This is the most commonly used join, focusing on the intersection of the tables.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
Example:
This query selects the first name of employees along with the name of their respective departments by joining the employees table with the departments table on the department_id column.
SELECT employees.first_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
Note: INNER JOIN can also be written simply as JOIN, as they are synonymous.
2. LEFT JOIN (or LEFT OUTER JOIN):
A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, 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:
This query selects the first name of employees along with the name of their respective departments. All employees are returned, even if they do not belong to any department.
SELECT employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
3. RIGHT JOIN (or RIGHT OUTER JOIN):
A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, 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:
This query selects the first name of employees along with the name of their respective departments. All departments are returned, even if they do not have any employees.
SELECT employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
4. FULL JOIN (or FULL OUTER JOIN):
A FULL JOIN returns all rows when there is a match in either the left or right table. If there is no match, NULL values are returned for columns from the other table.
Syntax:
SELECT columns
FROM table1
FULL JOIN table2 ON table1.column_name = table2.column_name;
Example:
This query selects the first name of employees along with the name of their respective departments. All employees and departments are returned, with NULL values for unmatched rows.
SELECT employees.first_name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;
