Joins

In SQL, joins are used to combine data from two or more tables based on a related column between them. When working with relational databases, data is often stored in multiple tables, and joins allow you to retrieve meaningful data by linking those tables together.

1. INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables.

SELECT employees.id, employees.username, employees.email, department.name AS department_name
FROM employees
INNER JOIN department ON employees.department = department.name;

2. LEFT JOIN (or LEFT OUTER JOIN)

A LEFT JOIN returns all rows from the left table (here, employees), and the matched rows from the right table (department). If there is no match, the result will contain NULL for columns from the right table.

SELECT employees.id, employees.username, employees.email, department.name AS department_name
FROM employees
LEFT JOIN department ON employees.department = department.name;

3. RIGHT JOIN (or RIGHT OUTER JOIN)

A RIGHT JOIN returns all rows from the right table (here, department), and the matched rows from the left table (employees). If there is no match, the result will contain NULL for columns from the left table.

SELECT employees.id, employees.username, employees.email, department.name AS department_name
FROM employees
RIGHT JOIN department ON employees.department = department.name;

4. FULL JOIN (or FULL OUTER JOIN)

A FULL JOIN returns all rows when there is a match in either the left (employees) or right (department) table. If there is no match, the result will contain NULL for the missing side.

5. CROSS JOIN

A CROSS JOIN returns the Cartesian product of both tables. It combines every row from the first table with every row from the second table. This can result in a very large result set.

SELECT employees.id, employees.username, department.name AS department_name
FROM employees
CROSS JOIN department;

6. SELF JOIN

A SELF JOIN is when a table is joined with itself. This is useful when you want to compare rows within the same table.

SELECT e1.id, e1.username AS employee1, e2.username AS employee2
FROM employees e1
INNER JOIN employees e2 ON e1.department = e2.department AND e1.id != e2.id;

SQL Joins Summary

Join Type	Description	Returns
INNER JOIN	Combines rows from both tables where there is a match on the join condition.	Only rows where there is a match in both tables.
LEFT JOIN (LEFT OUTER JOIN)	Returns all rows from the left table, and matching rows from the right table. If no match, NULL is returned for columns from the right table.	All rows from the left table and matched rows from the right table. Non-matching rows from the right table will have NULL values.
RIGHT JOIN (RIGHT OUTER JOIN)	Returns all rows from the right table, and matching rows from the left table. If no match, NULL is returned for columns from the left table.	All rows from the right table and matched rows from the left table. Non-matching rows from the left table will have NULL values.
FULL JOIN (FULL OUTER JOIN)	Returns all rows from both tables, with NULL for columns from the table where there is no match.	All rows from both tables. Rows from both tables that don't have a match will have NULL values in the columns from the other table.
CROSS JOIN	Returns the Cartesian product of both tables, combining every row from the first table with every row from the second table.	Every possible combination of rows from the left and right tables. Can result in a very large number of rows.
SELF JOIN	A table is joined with itself, useful for comparing rows within the same table.	Matches rows from the same table based on a condition. Often used for hierarchical or recursive relationships.