>_ IzzyLab

Hands-on task

This task is step 7 of the course “SQL with SQLite”

SQLite: combine two sets of rows (UNION)

easy~5 min

The database /root/users.db has two tables: active_users(name TEXT) and archived_users(name TEXT). Write a query with UNION that returns a combined list of names without duplicates (the same name may appear in both tables), and write the result to /root/all_users.txt — one name per line.

What you'll practise:
  • union
  • combining result sets
  • deduplication
  • select
  • sqlite3

Topic breakdown

The UNION operator glues the results of several SELECTs into one set of rows while removing duplicates; its variant UNION ALL, on the contrary, keeps all repeats. A mandatory condition is that all combined selects must return the same number of columns of compatible types. The choice between the two forms is dictated by whether duplicates are needed in the result or not. The sqlite3 result is written to a file with an ordinary > redirect.

What's checked

  1. all unique names are present
  2. duplicates removed

What it looks like