UNION and UNION ALL

The last corridor of the Labyrinth teaches combining sets. So far each query returned a single list. Now we will take two queries and glue their results into one list using the UNION and UNION ALL operators.

UNION - combining sets

The UNION operator glues the results of two queries into one list. Both queries must return the same number of columns of compatible types:

1SELECT tytul FROM ksiazki WHERE kategoria_id = 1
2UNION
3SELECT tytul FROM ksiazki WHERE kategoria_id = 2;

UNION

removes duplicates - if the same title appears in both queries, it shows up only once.

UNION ALL - with duplicates

UNION ALL

works the same way but keeps all rows, including repetitions:

1SELECT tytul FROM ksiazki WHERE kategoria_id = 1
2UNION ALL
3SELECT tytul FROM ksiazki WHERE kategoria_id = 2;

It is faster, because the Library does not have to find and remove duplicates. Choose it when duplicates do not bother you.

Combining different tables

UNION does not require both queries to come from the same table. A matching number and types of columns is enough:

1SELECT imie FROM autorzy
2UNION
3SELECT imie FROM czytelnicy;

This gives us one list of first names - both the creators and the readers of the Library.

@name