In SQL, joins combine rows from two or more tables based on related columns. This is crucial when working with normalized databases to extract meaningful insights.

1. INNER JOIN

An INNER JOIN fetches only the rows with matching values in both tables.

Example:

Consider these tables:

Query to perform INNER JOIN

SELECT Customers.Name, Orders.Product FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

2. LEFT JOIN (or LEFT OUTER JOIN)

A LEFT JOIN retrieves all rows from the left table and matching rows from the right table. Non-matching rows will have NULL in the columns of the right table.

Example:

Query:

SELECT Customers.Name, Orders.Product  
FROM Customers  
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;  

3. RIGHT JOIN (or RIGHT OUTER JOIN)

A RIGHT JOIN retrieves all rows from the right table and matching rows from the left table. Non-matching rows will have NULL in the columns of the left table.

Example:

Query:

SELECT Customers.Name, Orders.Product  
FROM Customers  
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;  

4. FULL JOIN (or FULL OUTER JOIN)

A FULL JOIN combines results of both LEFT and RIGHT JOINs. Rows with no match in either table are included with NULL values.

Example:

Query:

SELECT Customers.Name, Orders.Product  
FROM Customers  
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

5. CROSS JOIN

A CROSS JOIN returns the Cartesian product of two tables, meaning every row from the first table is paired with every row from the second table.

Example:

Query:

SELECT Customers.Name, Orders.Product  
FROM Customers  
CROSS JOIN Orders;  

.

Key Takeaways

• Use INNER JOIN for strict matches.
• Use LEFT JOIN to include unmatched rows from the left.
• Use RIGHT JOIN for unmatched rows from the right.
• Use FULL JOIN to see unmatched rows from both sides.
• Use CROSS JOIN for Cartesian products (rare in practice).