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 Set Operations Review and Practice

Posted by Ahmed Mohamed Fouad
Ahmed Mohamed Fouad
11,735 Points

SQL Playground - Set Operations Practice (second question)

The Question:

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

My Answer:

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

and I don't think this answer is true, would you please help ?

Mark Chesney
Mark Chesney
11,747 Points

Hi Ahmed,

I really liked your answer... because I came up with the same one too...unfortunately, with no success :\

As you've probably observed (but this is for everyone else's knowledge), the following two queries return the same results:

-- long query
SELECT title FROM books_north 
EXCEPT 
SELECT title FROM books_south 
UNION 
SELECT title FROM books_south 
EXCEPT 
SELECT title FROM books_north;

-- second half only (which overwrites the first half)
SELECT title FROM books_south 
EXCEPT 
SELECT title FROM books_north;

My only solution to this same question is found in two separate queries, unfortunately:

-- Create a list of unique books. 
-- Books that are in the north or south location, but not in both locations.
SELECT title FROM books_south
EXCEPT
SELECT title FROM books_north;
SELECT title FROM books_north
EXCEPT
SELECT title FROM books_south

7 Answers

Steven Parker
Steven Parker
229,732 Points
Steven Parker
Steven Parker
229,732 Points

You're close, but you need that last EXCEPT to occur before the UNION. A sub-query or derived table would help:

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

But those techniques haven't been introduced yet. Without skipping ahead, you can do it using a JOIN:

SELECT title FROM books_south UNION SELECT title FROM books_north
EXCEPT
SELECT books_south.title FROM books_south
    INNER JOIN books_north ON books_south.title = books_north.title;
Ahmed Mohamed Fouad
Ahmed Mohamed Fouad
11,735 Points
Ahmed Mohamed Fouad
Ahmed Mohamed Fouad
11,735 Points

Thanks Steven, your answers are always the best, easy to understand & straight to the point

ryanosten
seal-mask
.a{fill-rule:evenodd;}techdegree
ryanosten
PHP Development Techdegree Student 29,615 Points
ryanosten
.a{fill-rule:evenodd;}techdegree
ryanosten
PHP Development Techdegree Student 29,615 Points

Im not understanding why this query doesnt work. I see the results are only those that are in south but not in north, but Im not understanding why. Shouldnt it union both (a) those that are in north but not in south, and (b) those that are in south but not in north?

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

ryanosten
seal-mask
.a{fill-rule:evenodd;}techdegree
ryanosten
PHP Development Techdegree Student 29,615 Points
ryanosten
.a{fill-rule:evenodd;}techdegree
ryanosten
PHP Development Techdegree Student 29,615 Points

I guess its something with order of operations

ryanosten
seal-mask
.a{fill-rule:evenodd;}techdegree
ryanosten
PHP Development Techdegree Student 29,615 Points
ryanosten
.a{fill-rule:evenodd;}techdegree
ryanosten
PHP Development Techdegree Student 29,615 Points

nvm I see why.

kevin hudson
PLUS
kevin hudson
Courses Plus Student 11,987 Points
kevin hudson
kevin hudson
Courses Plus Student 11,987 Points

I might be wrong here but my answer ( after reviewing both tables manually) only produced three results which appear to be true thus the correct answer should be:

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

SELECT title,author FROM books_north EXCEPT 
SELECT title,author FROM books_south ORDER BY title;

answer results

Steven Parker
Steven Parker
229,732 Points
Steven Parker
Steven Parker
229,732 Points

That gets you the 3 books in the North location that are not in the South, but there are 3 more in the South that are not in the North. The total list of unique books will have 6 items.

kevin hudson
PLUS
kevin hudson
Courses Plus Student 11,987 Points
kevin hudson
kevin hudson
Courses Plus Student 11,987 Points

Yeah, I spoke too soon but decided to leave my answer to further help understand the problem. I believe the question feels like it's out of one's scope if you are following along you have to include an INNER JOIN somewhere and, as you stated, the subquery example will work but wasn't covered at that time. I am just now going over subqueries.