LuisLlamas Logo
joins-multiples-tablas

Multiple Table Joins in SQL

  • 3 min
  • Intermediate

The JOIN clause allows you to concatenate multiple tables to combine information distributed across three, four, or more relational tables within a single query.

So far, we’ve seen how to match Customers with their Orders. But the real world is more complex.

You don’t just want to know the Order ID; you want to know the Product Name, the Category it belongs to, and the Employee who sold it. That data lives in 4 or 5 different tables. How do we join them all?

The good news is that SQL has no (theoretical) limit on how many tables you can join. The syntax is simply a chain.

The Basic Chain

Imagine a train. The first car is coupled to the second. The second is coupled to the third. The first doesn’t touch the third directly; they are connected through the second.

In SQL it’s the same:

  1. You take Table A.
  2. You join it with Table B.
  3. The result of (A + B) is joined with Table C.
SELECT 
    Clientes.Nombre AS Cliente,
    Pedidos.Fecha AS FechaPedido,
    Empleados.Nombre AS Vendedor
FROM Clientes             -- Car 1

INNER JOIN Pedidos        -- Car 2 (Joins to 1)
    ON Clientes.ClienteID = Pedidos.ClienteID

INNER JOIN Empleados      -- Car 3 (Joins to 2)
    ON Pedidos.EmpleadoID = Empleados.EmpleadoID;
Copied!

Notice that in the second ON, we join Pedidos with Empleados.

Clientes has nothing to do there anymore; its job ended at the first coupling.

Many-to-Many Relationships

This is a very frequent case for multiple joins. Imagine Students and Courses.

  • A student enrolls in many courses.
  • A course has many students.

In databases, we cannot join these two tables directly. We need an Intermediate Table (or bridge/pivot table), let’s call it Enrollments.

To find out “What courses does Ana study?”, we have to cross 3 tables: Students -> Enrollments -> Courses.

SELECT 
    Estudiantes.Nombre AS Estudiante,
    Asignaturas.Nombre AS Asignatura
FROM Estudiantes

INNER JOIN Matriculas      -- The Bridge
    ON Estudiantes.EstudianteID = Matriculas.EstudianteID

INNER JOIN Asignaturas     -- The Destination
    ON Matriculas.AsignaturaID = Asignaturas.AsignaturaID;
Copied!

If you forget the intermediate table and try to join Students with Courses directly… you’ll discover you don’t have any common column (ON ... ???) to make the connection.

The Danger of Mixing INNER and LEFT

The order of operations in SQL does change the product when you mix Join types.

Imagine this scenario:

  1. We want to list ALL customers (LEFT JOIN Orders).
  2. And we want to see the salesperson’s name for the order (INNER JOIN Employees).

The ❌ INCORRECT WAY (Breaks the LEFT JOIN)

SELECT Clientes.Nombre, Pedidos.PedidoID, Empleados.Nombre
FROM Clientes

LEFT JOIN Pedidos ON Clientes.ClienteID = Pedidos.ClienteID
INNER JOIN Empleados ON Pedidos.EmpleadoID = Empleados.EmpleadoID;
Copied!

What happens here?

  1. The LEFT JOIN works: It brings “Juan” (who has no orders). P.EmpleadoID is NULL for Juan.
  2. The INNER JOIN arrives: It tries to join that NULL with the Employees table. Since NULL is not equal to any Employee ID, Juan’s row is eliminated.

By adding the last INNER JOIN, you have implicitly turned your previous LEFT JOIN into an INNER JOIN. You have lost the customers without purchases.

The ✅ Correct way (Maintains the chain of nulls)

SELECT Clientes.Nombre, Pedidos.PedidoID, Empleados.Nombre
FROM Clientes

LEFT JOIN Pedidos ON Clientes.ClienteID = Pedidos.ClienteID
LEFT JOIN Empleados ON Pedidos.EmpleadoID = Empleados.EmpleadoID;
Copied!

If you start with LEFT JOIN, you should generally continue with LEFT JOIN for the rest of the chain to maintain the nulls.

On this page