Databases Querying Relational Databases Set Operations Review and Practice

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 ?

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
202,001 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;

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 25,503 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;

kevin hudson
PRO
kevin hudson
Pro Student 9,652 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
202,001 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
PRO
kevin hudson
Pro Student 9,652 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.