SQL and MySQL course Β· Module 8: Subqueries

UNION and UNION ALL

2 min read Β·
In this lesson3

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:

SELECT tytul FROM ksiazki WHERE kategoria_id = 1
UNION
SELECT 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:

SELECT tytul FROM ksiazki WHERE kategoria_id = 1
UNION ALL
SELECT 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:

SELECT imie FROM autorzy
UNION
SELECT imie FROM czytelnicy;

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

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What does the UNION operator do with the results of two queries?

  2. 2. What condition must two queries combined with the UNION operator meet?

These are 2 of 3 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Click in order

    Click the elements in the order in which we build a query that joins two lists of titles with the UNION operator:

  • Code editor

    Write a query that uses UNION to combine the titles of books from the category with kategoria_id = 1 and the titles of books from the category with kategoria_id = 2. In both queries select the tytul column from the ksiazki table.

  • Vertical ordering

    Arrange (top to bottom) the lines of the query joining two lists of titles with the UNION ALL operator:

  • Code editor

    Write a query that uses UNION ALL to combine the titles of books from the category with kategoria_id = 1 and the titles of books from the category with kategoria_id = 2. In both queries select the tytul column from the ksiazki table.

  1. QuestionHow UNION works
  2. QuestionUNION requirement
  3. QuestionUNION ALL
  4. Click in orderTitles of two categories with UNION
  5. Coding taskCombine titles from two categories
  6. Put in orderTitles of two categories with UNION ALL
  7. Coding taskCombine titles with duplicates

Useful articles