LuisLlamas Logo
inner-right-full-join-sql

Table Joins in SQL with INNER, LEFT, RIGHT, and FULL JOIN

  • 4 min
  • Beginner

In the previous article, we introduced using JOIN as a mechanism to obtain data combinations between two or more tables.

We mentioned that there are four main types of JOIN: INNER, LEFT, RIGHT, and FULL, each with its different behavior.

FROM Left_Table AliasA
[TYPE] JOIN Right_Table AliasB 
    ON AliasA.Column = AliasB.Column
Copied!

The syntax for all four is very similar. The TYPE we specify changes, as well as the value mapping in the ON.

To understand this, nothing is better than seeing JOINs in action 👇.

Intersection INNER JOIN

It is the most common type of join. When we simply say “do a JOIN”, we are referring to this one.

The INNER JOIN returns only the rows where there is a match in both tables.

If a record from table A does not have its counterpart in table B, that record is discarded.

For example, it would be a query like:

Give me all customers who HAVE orders (and the orders)

Let’s see an example,

SELECT 
    C.Nombre AS Cliente, 
    P.Fecha, 
    P.Total
FROM Clientes C
INNER JOIN Pedidos P ON C.ClienteID = P.ClienteID;
Copied!
  • If you have a Customer “Juan” who has never bought anything: Does not appear.
  • If you have an orphaned Order (without an associated customer): Does not appear.
  • Only perfect matches appear.

Although the full keyword is INNER JOIN, T-SQL allows writing simply JOIN. It is exactly the same, but for clarity, it’s better to write INNER JOIN.

Left Priority LEFT JOIN

Here we begin to deal with asymmetry. The LEFT JOIN (or equivalent LEFT OUTER JOIN)

  • Returns all rows from the left table
  • And the matching rows from the right table.

What if there is no match? SQL Server fills the right table columns with NULL.

Give me ALL Customers, regardless of whether they have bought anything or not

If they have bought, add the order. If not, show them anyway with a gap

Which in SQL would be something like this,

SELECT 
    C.Nombre AS Cliente, 
    P.PedidoID
FROM Clientes C  -- Left Table (Main)
LEFT JOIN Pedidos P ON C.ClienteID = P.ClienteID; -- Right Table
Copied!

Result:

  1. Ana (Has order 101) -> Appears: Ana | 101
  2. Juan (Has no orders) -> Appears: Juan | NULL

This JOIN can also be used to find absences. For example: “Which customers haven’t bought anything?”

SELECT C.Nombre
FROM Clientes C
LEFT JOIN Pedidos P ON C.ClienteID = P.ClienteID
WHERE P.PedidoID IS NULL; -- We filter those that didn't match
Copied!

Right Priority RIGHT JOIN

The RIGHT JOIN is exactly the opposite of LEFT JOIN.

  • Returns all rows from the right table,
  • And the matching ones from the left.

If there is no match on the left, it fills with NULL.

Give me ALL Orders, even if they don’t have a Customer assigned

Which would be something like this,

SELECT 
    C.Nombre AS Cliente, 
    P.PedidoID
FROM Clientes C
RIGHT JOIN Pedidos P ON C.ClienteID = P.ClienteID;
Copied!

In practice, RIGHT JOIN is very rarely used. Why? Because we read mentally from left to right.

You can convert a RIGHT into a LEFT by inverting the order of the tables in the FROM, and it’s more natural.

That is: TablaA RIGHT JOIN TablaB is equal to TablaB LEFT JOIN TablaA.

Full Union FULL JOIN

The FULL JOIN (equivalent to FULL OUTER JOIN) is the sum of the two previous ones. It returns all rows from both tables.

  • If they match, it shows the joined data.
  • If it’s in A but not in B: Shows A and fills B with NULL.
  • If it’s in B but not in A: Shows B and fills A with NULL.

Which would be something like,

Give me EVERYTHING: Customers without orders, Orders without customers, and Customers with Orders. Don’t let anything be missing, give me everything.

Or in SQL,

SELECT 
    C.Nombre AS Cliente, 
    P.PedidoID
FROM Clientes C
FULL JOIN Pedidos P ON C.ClienteID = P.ClienteID;
Copied!

It is very useful for data cleaning tasks or reconciliation reports to see which records don’t match between two systems.

Cartesian Product CROSS JOIN

There is a fifth type, less common but dangerous if done by accident: the CROSS JOIN (Cartesian product).

This JOIN has no ON condition. It crosses each row of table A with each row of table B. If Customers has 100 rows and Products has 100 rows, the result will be 10,000 rows ().

SELECT C.Nombre, P.NombreProducto
FROM Clientes C
CROSS JOIN Productos P;
Copied!

Use it only when you want to generate massive combinations (for example, creating a calendar by crossing “Days” with “Shifts”). If you use it by mistake on large tables, you can crash the server 💥.

On this page