Table joins (JOINs) in SQL are used to combine related data from multiple tables. Since relational database design splits tables (normalization) to eliminate data redundancy, joins are a critical operation.
1. INNER JOIN
An inner join returns only the rows from both tables that satisfy the join condition. It is the most commonly used join type.
SELECT column_list
FROM table1
INNER JOIN table2
ON join_condition;
table1,table2: Names of the tables to join.join_condition: Specifies the criteria for the join. Typically compares columns common to both tables (such as primary key and foreign key relationships) using=.
Example:
Join the orders table and customers table by customer_id to retrieve order information and customer names.
SELECT
o.order_id,
o.order_date,
c.customer_name
FROM
orders AS o
INNER JOIN
customers AS c
ON
o.customer_id = c.customer_id;
NATURAL JOIN
NATURAL JOIN automatically uses columns with the same name in both tables as the join key. There is no need to explicitly specify the join condition with an ON clause.
SELECT column_list
FROM table1
NATURAL JOIN table2;
Caution: All columns with matching names are used as join conditions, which may lead to unintended joins. Using explicit INNER JOIN with an ON clause is recommended.
2. OUTER JOIN
An outer join returns rows that satisfy the join condition plus rows from the specified side that do not match. Columns from the non-matching side are filled with NULL.
LEFT OUTER JOIN (LEFT JOIN)
Returns all rows from the left table (specified in FROM) and matching rows from the right table. If there is no match in the right table, right-side columns contain NULL.
SELECT column_list
FROM table1
LEFT OUTER JOIN table2
ON join_condition;
-- or (OUTER is optional)
SELECT column_list
FROM table1
LEFT JOIN table2
ON join_condition;
Example: Retrieve all customers and their order information. Customers without orders are also displayed.
SELECT
c.customer_name,
o.order_id,
o.order_date
FROM
customers AS c
LEFT JOIN
orders AS o
ON
c.customer_id = o.customer_id;
RIGHT OUTER JOIN (RIGHT JOIN)
Returns all rows from the right table (specified after JOIN) and matching rows from the left table. If there is no match in the left table, left-side columns contain NULL.
SELECT column_list
FROM table1
RIGHT OUTER JOIN table2
ON join_condition;
-- or (OUTER is optional)
SELECT column_list
FROM table1
RIGHT JOIN table2
ON join_condition;
Example: Retrieve all order information and the customer names for those orders.
SELECT
c.customer_name,
o.order_id,
o.order_date
FROM
customers AS c
RIGHT JOIN
orders AS o
ON
c.customer_id = o.customer_id;
Note: RIGHT JOIN can achieve the same result as LEFT JOIN by swapping the table order, so LEFT JOIN is generally used more frequently.
3. CROSS JOIN
A cross join returns all combinations (Cartesian product) of rows from both tables without specifying a join condition. The result set has “table1 rows x table2 rows” number of rows.
SELECT column_list
FROM table1
CROSS JOIN table2;
Example:
Cross join the colors and sizes tables to generate all color-size combinations.
SELECT
c.color_name,
s.size_name
FROM
colors AS c
CROSS JOIN
sizes AS s;
Cross joins are useful for generating specific combinations or creating test data.