SQL Joins

SQL Joins are used to combine rows from two or more tables based on a related column between them. Here are the most common types of SQL joins:

1. INNER JOIN
2. LEFT JOIN (or LEFT OUTER JOIN)
3. RIGHT JOIN (or RIGHT OUTER JOIN)
4. FULL JOIN (or FULL OUTER JOIN)
5. CROSS JOIN

Let's use two example tables to illustrate these joins:

Tables

Table: employees

Table: departments

1. INNER JOIN

Returns only the rows that have matching values in both tables.

Query:

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Output:

2. LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table, and the matched rows from the right table. If no match is found, NULLs are returned for columns from the right table.

Query:

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

Output:

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table, and the matched rows from the left table. If no match is found, NULLs are returned for columns from the left table.

Query:

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

Output:

4. FULL JOIN (or FULL OUTER JOIN)

Returns all rows when there is a match in either left or right table. Rows without a match in the other table will contain NULLs.

Query:

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;

Output:

5. CROSS JOIN

Returns the Cartesian product of both tables, i.e., all possible combinations of rows.

Query:

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
CROSS JOIN departments;

Output:

These examples illustrate how SQL joins work and how they can be used to combine data from multiple tables based on specific conditions.