SQL joins

SQL Joins: Explained with Examples

Introduction to SQL Joins

SQL joins are used to combine rows from two or more tables based on a related column between them. They allow you to retrieve data from multiple tables in a single query.

For this tutorial, let's consider two sample tables:

Customers Table

CustomerID	CustomerName
1	Alice
2	Bob
3	Charlie

Orders Table

OrderID	CustomerID
101	1
102	1
103	2

INNER JOIN

The INNER JOIN returns only the rows that have matching values in both tables. It filters out non-matching rows.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
    

Output:

CustomerName	OrderID
Alice	101
Alice	102
Bob	103

LEFT JOIN

The LEFT JOIN returns all rows from the left table and matching rows from the right table. If there's no match, NULL values are returned.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
    

Output:

CustomerName	OrderID
Alice	101
Alice	102
Bob	103
Charlie	null

RIGHT JOIN

The RIGHT JOIN returns all rows from the right table and matching rows from the left table. If there's no match, NULL values are returned.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
    

Output:

CustomerName	OrderID
Alice	101
Alice	102
Bob	103

FULL JOIN

The FULL JOIN returns all rows when there is a match in either the left or the right table. If there's no match, NULL values are returned.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
    

Output:

CustomerName	OrderID
Alice	101
Alice	102
Bob	103
Charlie	null
null	104

CROSS JOIN

The CROSS JOIN returns the Cartesian product of two tables, generating all possible combinations of rows from both tables.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders;
    

Output:

CustomerName	OrderID
Alice	101
Alice	102
Alice	103
Bob	101
Bob	102
Bob	103
Charlie	101
Charlie	102
Charlie	103