Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases Querying Relational Databases Subqueries Review and Practice

Philip Schultz
Philip Schultz
11,437 Points
Posted by Philip Schultz
Philip Schultz
11,437 Points

Why does UNION ALL work in this case and not just UNION

I'm not understanding why UNION ALL works in the subquery and not just UNION. I thought UNION will not show repeating data from the two tables. I'm missing something and I don't know what it is exactly. Any help would be much appreciated.

"Generate a report that lists the book titles from both locations and count the total number of books with the same title."

SELECT genre, title, COUNT(*) FROM (SELECT title, genre FROM books_north UNION ALL  SELECT title, genre FROM books_south) GROUP BY title ORDER BY title DESC;
'''

2 Answers

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

Your understanding of how UNION works is correct, and that's exactly the reason it doesn't work here..

With UNION, the sub-query would return only one row for each title so the outer query would not be able to get a count of duplicates.

Philip Schultz
Philip Schultz
11,437 Points
Philip Schultz
Philip Schultz
11,437 Points

Got it! Thanks for the quick response.

lukej
lukej
34,222 Points
lukej
lukej
34,222 Points

I had the same issue. There a few loan records that are exactly the same in both the south and north library location.

For some reason Andrew Chalkley went to both locations on the same day and loaned out a copy of "Congo" twice which he never returned. Almost as if he didn't want anybody to read it. Suspicious, isn't it? ;)