SQL JOIN Three Tables

SELECT
    Customers.first_name,
    Orders.item,
    Shippings.status
FROM Customers
LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id
LEFT JOIN Shippings ON Orders.customer_id = Shippings.customer

Join Three Tables

The most common types of joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Let's look at an example using INNER JOIN.

SELECT
    Customers.first_name,
    Customers.last_name,
    Orders.item,
    Shippings.status
FROM
    Customers
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id
INNER JOIN Shippings ON Orders.customer_id = Shippings.customer

Here, the SQL command

• joins the Customers table with the Orders table where their customer_id fields match.
• joins the Orders table with the Shippings table where the customer_id in Orders matches the customer field in Shippings.
• selects the first name and last name of the customer from the Customers table.
• selects the item from the Orders table.
• selects the shipping status from the Shippings table.

SQL Join Three Tables With AS Alias

We can use AS aliases with table names to make our query short and clean. For example,

SELECT
    c.first_name,
    c.last_name,
    o.item,
    o.amount,
    s.status
FROM
    Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN Shippings s ON c.customer_id = s.customer

Here, the SQL command joins three tables: Customers, Orders, and Shippings. The Customers table is aliased as c, Orders as o, and Shippings as s.

This makes our query short and clean.