Learn SQL
UNION
44.4
Lesson

UNION ALL

Let’s say we want to print id cards for all the students and the books. Each id card for a student will just have his id, and each id card for a book will just contain its id. Now, say we have to print all the ids in a single result, under a single column.

This looks like a perfect use case for UNION query. Go ahead and run the query below.

Notice that we have 34 records in the results. But we expect a total of ids of 25 students and 34 books. The ids for students range from 1 to 25, and for books, range from 1 to 34. The results of the above query are just 1 to 34. This goes on to explain that if there are records exactly matching from multiple queries in a UNION, only one of them will be included in the final results. That’s another way of saying that the results of a UNION are always unique.

Then how do we solve our problem? We need all 25 student ids and all 34 book ids. In such cases, there is an extension to UNION, called UNION ALL. Run the query below:

Notice that by using UNION ALL, we get all (25 + 34) results, as expected.