With SQL, there are various JOIN types that let you combine data from different tables. Below is a quick summary of the most popular JOIN kinds and their syntax:
INNER JOIN: An INNER JOIN returns only the rows that match the join condition in both tables.
Syntax:
SELECT *
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Example:
SELECT employees.id, employees.name, department.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;
LEFT JOIN (or LEFT OUTER JOIN): A LEFT JOIN returns all rows from the left table (table1), even if there is no match in the right table (table2).
Syntax:
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
Example:
SELECT employees.id, employees.name, department.name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
RIGHT JOIN (or RIGHT OUTER JOIN): A RIGHT JOIN returns all rows from the right table (table2), even if there is no match in the left table (table1).
Syntax:
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
Example:
SELECT employees.id, employees.name, department.name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;
FULL JOIN (or FULL OUTER JOIN): A FULL JOIN returns all rows from both tables, even if there is no match in either table.
Syntax:
SELECT *
FROM table1
FULL JOIN table2
ON table1.column = table2.column;
Example:
SELECT employees.id, employees.name, department.name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.id;
Data from different tables can be combined using JOINs if they share a column or collection of columns. They are a crucial component of SQL and are commonly used in reporting and data analysis.