7. Joins

Joins allow us to run queries whose result can be made up of columns from different tables. This is possible because the tables hold a foreign key, they are related in some way. This way we can run queries with data from both tables. However, this utility must be used with caution since it consumes a lot of resources.

For the lesson I have created a minimalist database with fruit and colors.

You can download it here.

The tables have the following structure:

erDiagram
    Frutas ||--o| Colores : "has color"
    Frutas {
        int FrutaId PK
        string Nombre
        int ColorId FK
    }
    Colores {
        int ColorId PK
        string Nombre
    }

Data in Frutas: Melocotón (ColorId=3), Kiwi (ColorId=4), Coco (ColorId=5), Higo (ColorId=NULL), Manzana (ColorId=NULL)

Data in Colores: 1-Rojo, 2-Amarillo, 3-Naranja, 4-Verde, 5-Marrón

INNER JOIN

The best known and most used. It joins the values that match in both tables if there is a relationship. Otherwise it omits the row. It is a horizontal merge, since it combines columns from 2 or more tables, expanding the results "to the sides".

SELECT [tabla.columnas] FROM [tabla_1] INNER JOIN [tabla_2] ON [tabla_1.columna] = [tabla_2.columna];

SELECT * FROM Frutas INNER JOIN Colores ON Frutas.ColorId = Colores.ColorId;

FrutaId | Nombre | ColorId | ColorIColorId | Nombre
1   Melocotón   3   3   Naranja
2   Kiwi    4   4   Verde
3   Coco    5   5   Marrón

To simplify the result we will give some aliases.

SELECT Frutas.Nombre AS Nombre, Colores.Nombre AS Color FROM Frutas INNER JOIN Colores ON Frutas.ColorId = Colores.ColorId;

Fruta | Color
Melocotón   Naranja
Kiwi    Verde
Coco    Marrón

Visually, INNER JOIN works like this:

flowchart LR
    A["Frutas
━━━━━━━
✓ Melocotón (3)
✓ Kiwi (4)
✓ Coco (5)
✗ Higo (NULL)
✗ Manzana (NULL)"]
    B["Colores
━━━━━━━
✗ 1-Rojo
✗ 2-Amarillo
✓ 3-Naranja
✓ 4-Verde
✓ 5-Marrón"]
    C["Result
━━━━━━━
Melocotón-Naranja
Kiwi-Verde
Coco-Marrón"]

    A -->|"INNER JOIN
Only matches"| C
    B -->|"INNER JOIN
Only matches"| C

Much better, right? Now you should be wondering: Where are the Higo and the Manzana? INNER JOIN is a diehard bachelor, it ignores relationships that lead nowhere or null rows.

LEFT JOIN

It joins the values of the first table with the second, even if it finds broken or Null relationships. Basically it does not omit any row from the first table. It is also a horizontal merge.

SELECT [tabla.columnas] FROM [tabla_1] LEFT JOIN [tabla_2] ON [tabla_1.columna] = [tabla_2.columna];

SELECT Frutas.Nombre AS Nombre, Colores.Nombre AS Color FROM Frutas LEFT JOIN Colores ON Frutas.ColorId = Colores.ColorId;

Fruta | Color
Melocotón   Naranja
Kiwi    Verde
Coco    Marrón
Higo    NULL
Manzana NULL

Visually, LEFT JOIN works like this:

flowchart LR
    A["Frutas (ALL)
━━━━━━━
✓ Melocotón (3)
✓ Kiwi (4)
✓ Coco (5)
✓ Higo (NULL)
✓ Manzana (NULL)"]
    B["Colores
━━━━━━━
✗ 1-Rojo
✗ 2-Amarillo
✓ 3-Naranja
✓ 4-Verde
✓ 5-Marrón"]
    C["Result
━━━━━━━
Melocotón-Naranja
Kiwi-Verde
Coco-Marrón
Higo-NULL
Manzana-NULL"]

    A -->|"LEFT JOIN
All from the left"| C
    B -->|"Only matches"| C

RIGHT JOIN

Same mechanism as the previous one but inverted. It joins the values of the second table with the first. In this case it does not omit rows from the second table. It is still a horizontal merge.

SELECT [tabla.columnas] FROM [tabla_1] RIGHT JOIN [tabla_2] ON [tabla_1.columna] = [tabla_2.columna];

SELECT Frutas.Nombre AS Nombre, Colores.Nombre AS Color FROM Frutas RIGHT JOIN Colores ON Frutas.ColorId = Colores.ColorId;

Fruta | Color
Melocotón   Naranja
Kiwi    Verde
Coco    Marrón
Higo    NULL
Manzana NULL

SQLite 3.12, and earlier versions, are not compatible with RIGHT JOIN, so we won't be able to see a real result. However, we will achieve the same effect with LEFT JOIN and the opposite table.

SELECT Frutas.Nombre AS Nombre, Colores.Nombre AS Color FROM Frutas LEFT JOIN Colores ON Frutas.ColorId = Colores.ColorId;

FULL JOIN

It does not ignore any row, neither from the first table nor from the second. Like its counterparts, it is a horizontal merge.

SELECT [tabla.columnas] FROM [tabla_1] FULL OUTER JOIN [tabla_2] ON [tabla_1.columna] = [tabla_2.columna];

SELECT Frutas.Nombre AS Nombre, Colores.Nombre AS Color FROM Frutas FULL OUTER JOIN Colores ON Frutas.ColorId = Colores.ColorId;

Fruta | Color
Melocotón   Naranja
Kiwi    Verde
Coco    Marrón
NULL    Rojo
NULL    Amarillo
Higo    NULL
Manzana NULL

Visually, FULL OUTER JOIN works like this:

flowchart LR
    A["Frutas (ALL)
━━━━━━━
✓ Melocotón (3)
✓ Kiwi (4)
✓ Coco (5)
✓ Higo (NULL)
✓ Manzana (NULL)"]
    B["Colores (ALL)
━━━━━━━
✓ 1-Rojo
✓ 2-Amarillo
✓ 3-Naranja
✓ 4-Verde
✓ 5-Marrón"]
    C["Result
━━━━━━━
Melocotón-Naranja
Kiwi-Verde
Coco-Marrón
Higo-NULL
Manzana-NULL
NULL-Rojo
NULL-Amarillo"]

    A -->|"FULL OUTER JOIN
All rows"| C
    B -->|"FULL OUTER JOIN
All rows"| C

SQLite 3.12, and earlier versions, are not compatible with FULL OUTER JOIN, so we won't be able to see its result.

SQL UNION

It combines the values of different queries into a single column or several. It also removes repetitions. It is considered a vertical merge since it stacks queries "downwards", adding up the rows.

SELECT [tabla.columnas] FROM [tabla_1]
UNION
SELECT [tabla.columnas] FROM [tabla_2];

It is necessary that the columns to be mixed are named with the same alias or name.

SELECT Frutas.nombre FROM Frutas
UNION
SELECT Colores.nombre FROM Colores

Nombre

Amarillo
Coco
Higo
Kiwi
Manzana
Marrón
Melocotón
Naranja
Rojo
Verde

If you need the data shown raw, without removing the repetitions, use UNION ALL.

SELECT [tabla.columnas] FROM [tabla_1]
UNION ALL
SELECT [tabla.columnas] FROM [tabla_2];

SELECT Frutas.nombre FROM Frutas
UNION ALL
SELECT Colores.nombre FROM Colores

Solutions