Explain different types of JOINs in SQL.

SQL JOINs are fundamental operations used to combine rows from two or more tables based on a related column between them. They are essential for retrieving meaningful data from relational databases, allowing you to create a comprehensive view of scattered information.

Introduction to SQL JOINs

In relational databases, data is often distributed across multiple tables to ensure normalization and reduce redundancy. A JOIN clause is used to combine rows from two or more tables, based on a common field between them. The type of JOIN determines which rows are kept from each table when a match is found or not found.

Types of SQL JOINs

INNER JOIN

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. It returns only the rows where the join condition is met in both tables, effectively discarding rows that do not have a match in the other table.

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN keyword returns all rows from the left table (table1), and the matching rows from the right table (table2). If there is no match in the right table, NULL is used for columns from the right table. It's often used when you want to see all entries from one table, and any related entries from another.

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN keyword returns all rows from the right table (table2), and the matching rows from the left table (table1). If there is no match in the left table, NULL is used for columns from the left table. This is essentially the mirror image of a LEFT JOIN.

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

FULL OUTER JOIN (or OUTER JOIN)

The FULL OUTER JOIN keyword returns all rows when there is a match in either table. It combines the results of both LEFT and RIGHT outer joins. If there are rows in either table that do not have matches in the other table, those rows will still be included, with NULL values for the columns of the table that lacked a match.

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

CROSS JOIN

A CROSS JOIN produces a Cartesian product of the tables involved in the join. This means it combines each row from the first table with every row from the second table. If table A has N rows and table B has M rows, a CROSS JOIN will result in N * M rows. It does not require a join condition.

SELECT products.product_name, colors.color_name
FROM products
CROSS JOIN colors;

SELF JOIN

A SELF JOIN is a regular join, but the table is joined with itself. It is used to combine rows with other rows in the same table. This is particularly useful for querying hierarchical data or comparing rows within the same table, often requiring table aliases to differentiate between the two instances of the table.

SELECT A.employee_name AS Employee, B.employee_name AS Manager
FROM employees A, employees B
WHERE A.manager_id = B.employee_id;