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 Database Normalization Set Theory and Relational Databases

Isis van der Plas
Isis van der Plas
13,445 Points

creating a list of unique books in the SQL playground

one of the exercises in the SQL playground with this video asks you to

-- Create a list of unique books. -- Books that are in the north or south location, but not in both locations.

I managed to get two different tables, each containing 3 unique titles, doing this:

SELECT books_north.title FROM books_north EXCEPT SELECT books_south.title FROM books_south; SELECT books_south.title FROM books_south EXCEPT SELECT books_north.title FROM books_north;

however, I can't seem to merge it into one table, when I put UNION, or UNION ALL in between the two statements, like this:

SELECT books_north.title FROM books_north EXCEPT SELECT books_south.title FROM books_south UNION SELECT books_south.title FROM books_south EXCEPT SELECT books_north.title FROM books_north;

only the second table comes up, but I want to have all 6 unique titles, not three!

Can anyone point me in the right direction?

thanks in advance!

3 Answers

Steven Parker
Steven Parker
230,235 Points

Your set operations are all performed in order, so the last one is the EXCEPT that removes all titles that are found in the north location.

But if you restructure the second query as a sub-query, you can then merge them successfully:

SELECT title FROM books_north EXCEPT SELECT title FROM books_south
UNION
SELECT * FROM (SELECT title FROM books_south EXCEPT SELECT title FROM books_north);

This answer worked for me. However, in the course, we haven't been introduced to sub-queries yet. So, I'm not sure where the instructor included a challenge at this level that requires us to use a sub-query.

Can anyone explain why "SELECT * FROM" is needed instead of just SELECT title FROM books_south EXCEPT SELECT title FROM books_north?

I thought UNION set operation will stack the two tables. The two separate SELECT statements generate the correct tables.

Steven Parker
Steven Parker
230,235 Points

As I told Isis, without the sub-query, the UNION is performed first and then the final EXCEPT removes all the north location items.

Jamie W.
PLUS
Jamie W.
Courses Plus Student 3,931 Points

I'd like to try a different approach to this problem. Is there a reason why SQL won't recognize books_north.title and books_south.title as columns in the following WHERE phrase?

 SELECT title FROM books_north
 UNION
 SELECT title FROM books_south WHERE books_north.title != books_south.title;

I get an error message saying "Error: no such column: books_north.title"

Steven Parker
Steven Parker
230,235 Points

The WHERE clause is applied to the nearest SELECT so it does not have access to books_north.