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
David Wright
4,437 PointsTask 2 of 3 UNION or JOIN two query results that include EXCEPT clauses
-- Create a list of only the unique books in both north and south locations
SELECT title FROM books_north
EXCEPT
SELECT title FROM books_south;
---Tried using UNION ALL and UNION here, but it didn't work
SELECT title FROM books_south
EXCEPT
SELECT title FROM books_north;
I can get the results of both EXCEPT operation queries, however, I can't figure out how to join these results.
Thank you for any guidance!
Charlie Harcourt
8,046 PointsHow would you get it so both tables link into one?
8 Answers
Alexander Nikiforov
Java Web Development Techdegree Graduate 22,175 PointsI spend a lot of time trying to figure out that, and the only solution is I found is with subqueries, which is next part of the course:
-- Create a list of only the unique books in both north and south locations
SELECT title FROM books_north
WHERE title NOT IN (
SELECT title FROM books_south
)
UNION ALL
SELECT title from books_south
WHERE title NOT IN (
SELECT title FROM books_north
);
Gives:
Armada
Ready Player One
The Martian
Congo
Jurassic Park
Sphere
And as you correcty mentioned, EXCEPT gives only parts of tables:
-- unique for books_north
SELECT title FROM books_north
EXCEPT
SELECT title FROM books_south;
Gives:
Armada
Ready Player One
The Martian
For 'south books':
-- unique for books_south
SELECT title FROM books_south
EXCEPT
SELECT title FROM books_north;
Gives:
Congo
Jurassic Park
Sphere
And the right answer probably only Andrew Chalkley knows
Jason Anello
Courses Plus Student 94,610 PointsHi Alexander,
This one took me a while too.
After seeing your solution, I thought either this problem belongs in the next stage or there must be some way to do it without subqueries.
Here's what I came up with:
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
I think this only uses what we've been taught up to this point.
Alexander Nikiforov
Java Web Development Techdegree Graduate 22,175 PointsYou are right.
Your solution is indeed without subqueries.
I haven't thought of using joins without foreign keys. Nice :)
One thing is sad though, that there is no simple query to do that.
It seems the problem is rather relevant and interesting, but in order to solve it ... one has to think a lot :)
Jason Anello
Courses Plus Student 94,610 PointsI was thinking the same thing.
It seems like you'd want to have a built in operation for this unless it's not that common of a thing to do.
I was looking through the set operations section of a discrete mathematics textbook and I did find out that there is a name for this operation. It's called a symmetric difference.
Here's a link in case you're interested: http://www.math-only-math.com/symmetric-difference-using-Venn-diagram.html
It shows the venn diagram and the definition for it in terms of simpler operations.
(A – B) ∪ (B – A)
Going back to David's attempt, if we were allowed to put parentheses around the EXCEPT operations and then put a UNION where his comment about the union was, that would pretty much be a translation of the definition from set theory into SQL.
Alexander Nikiforov
Java Web Development Techdegree Graduate 22,175 PointsThanks for the link :)
It is always good to know maths.
I googled a bit, and I may be wrong, but I think grouping, like
SELECT title FROM books_north
EXCEPT
SELECT title FROM books_south;
UNION (
SELECT title FROM books_south
EXCEPT
SELECT title FROM books_north
)
Is available but not for every SQL databases. Take a look here:
http://stackoverflow.com/questions/15936507/does-standard-sql-allow-grouping-of-union-expressions
So quoting from there, grouping with UNION will be available with
But I have found that MySQL and SqlLite do not support it (for reference, Oracle, SQL Server, Postgres and DB2 do.)
This kind of thing also can be found in SQL Server, here take a look they use parenteses with UNION:
https://msdn.microsoft.com/en-us/library/ms180026.aspx
Apparently this is not standart.
But what I also see clear right now, that Symmetric Difference is indeed just an opposite of INTERSECT,
So actually would be nice if someone implemented in SQL stuff like
SELECT title FROM books_north
NOT INTERSECT
SELECT title FROM books_south
That would be great one :)
Allan Oloo
8,054 Pointsreally helpful thanks!
Alan McClenaghan
Full Stack JavaScript Techdegree Graduate 56,501 PointsIt's really annoying when they give an example that requires you know information that you haven't covered yet. It make you feel like you've missed something.
Steven Parker
231,271 PointsThe solution I posted doesn't require anything that had not been covered yet.
Steven Parker
231,271 PointsThis answer is a bit late 
-- Books that are in the north or south location, but not in both locations.
SELECT title FROM
(SELECT title FROM books_north EXCEPT SELECT title FROM books_south)
UNION ALL
SELECT title FROM
(SELECT title FROM books_south EXCEPT SELECT title FROM books_north)
Giuseppe Ardito
14,130 PointsGiven that the exercise wanted us to do that, I find this to be the most elegant solution.
Jason Anello
Courses Plus Student 94,610 PointsThe only issue though is that we don't learn about derived tables until the next section.
Giuseppe Ardito
14,130 PointsYes, in fact I also believe the exercise meant to be different. In your solution, Jason, can you tell me which operation will be processed first? The UNION or the JOIN? I can't figure it out. Can you break down the way it works?
Jason Anello
Courses Plus Student 94,610 PointsHi Giuseppe,
I don't know the details of how the engine works internally and what exactly is processed first.
But the UNION would be processed before the EXCEPT and the EXCEPT can't be processed until the JOIN is processed.
Giuseppe Ardito
14,130 PointsGreat. This clarifies! Thanks a lot. Do you know in what documentation we can actually see with what priority these operations are processed?
Jason Anello
Courses Plus Student 94,610 PointsI believe that SQLite is used in this course so I'll link to that documentation.
https://www.sqlite.org/lang_select.html
This page contains a series of diagrams/flow-charts which show you how to construct valid sql statements as well as explanations of the various types of statements and operations.
If you scroll near the bottom, you should find a section titled "Compound Select Statements". This section would be relevant to the query I gave in my comment to Alexander's answer.
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
Quoting the final paragraph in that section:
When three or more simple SELECTs are connected into a compound SELECT, they group from left to right. In other words, if "A", "B" and "C" are all simple SELECT statements, (A op B op C) is processed as ((A op B) op C).
This paragraph tells us that the UNION in the example above will be performed before the EXCEPT because that's what you would get to first when going from left to right. Imagine that the query was written out as one long line.
Giuseppe Ardito
14,130 PointsThank you Jason!
Dan Coleman
2,292 PointsHave a feeling that the questions are written incorrectly. It would make a lot more sense at this skill level if,
Task 1 - Create a list of all books in both north and south locations (UNION) Task 2 - Create a list of all unique books in both north and south locations (UNION ALL) Task 3 - Create a list of books that are in both north and south locations (INTERSECT)
Christian Scherer
2,989 PointsI had the same feeling, somehow the questions seem kinda ambiguous. Could one of the teachers clarify?
Krishna Guggilla
1,468 PointsThis is the way I solved task 2 Books that are in north and south location but not in both locations
SELECT title FROM books_south UNION SELECT title FROM books_north EXCEPT SELECT bn.title FROM books_north AS bn INNER JOIN books_south AS bs ON bs.title=bn.title
Steven Parker
231,271 PointsI believe that's the same answer (other than table aliases) that was already proposed by Jason on Oct 12.
nfs
35,526 PointsThe one thing I'm sure of is that there are a few different ways of solving problems with SQL too...
Patriot Rika
3,739 PointsI think the 3rd task is easier than it looks. It says find duplicate books.-- Create a list of books that are in both north and south locations. That are both in north and south means , if you go north you will find that book if you go south you will find that book also. So you are searching books that have same name etc.
SELECT title FROM books_north INTERSECT SELECT title FROM books_south;
INTERSECT finds duplicates from both data sets.
Gregory Ledger
6,116 PointsThanks for this answer. Would have taken days on my own to figure it out. It would be helpful to know if there is an order to these operations: seems as if the first thing SQL does is to implement the INNER JOIN on the last SELECT statement, maybe reserves that in memory, Then it would figure out the first UNION on the first two SELECT statements, then finally run the EXCEPT statement.
Kareem Jeiroudi
14,984 PointsHave a look on my solution. I think the only way to do it is either by subqueries which are taught right after this stage, or CTEs (Common Table Expressions). I created two different solution that gave a correct result. Additionally, read the comments above each query, to see how you can reason about this problem, if you'd like to.
-- Create a list of unique books.
-- Books that are in the north or south location, but not in both locations.
-- solution nom 01 -> Works!
-- 1. select those books that exist in both the north and south (Intersection).
-- 2. remove (except) the previously selected books from the *union* of books in the north and south.
WITH north_south_intersection AS (
SELECT title FROM books_north
INTERSECT
SELECT title FROM books_south
), north_south_union AS (
SELECT title FROM books_south
UNION
SELECT title FROM books_north
)
SELECT title FROM north_south_union
EXCEPT
SELECT title FROM north_south_intersection;
-- Solution nom 02 -> Works!
-- 1. Select the books that exist in the north but not the south
-- 2. Select the books that exist in the south but not the north.
-- 3. Union theses two groups together.
WITH north_except_south AS (
SELECT title FROM books_north
EXCEPT
SELECT title FROM books_south
), south_except_north AS (
SELECT title FROM books_south
EXCEPT
SELECT title FROM books_north
)
SELECT title FROM north_except_south
UNION
SELECT title FROM south_except_north;
-- check one title exists in both tables - but it shouldn't
-- so if any of these queries didn't return a result, it means it worked.
SELECT title FROM books_south
WHERE title = "<title>";
SELECT title FROM books_north
WHERE title = "<title>";
Marco Fregoso
858 PointsI think this solves the 2nd task in the simplest way possible
-- Create a list of only the unique books in both north and south locations
SELECT title, author FROM books_north
INTERSECT
SELECT title, author FROM books_south;
This will select and return unique books, that is books that you can find in both libraries and since books are checked one against another to find similar books, it will only return one book, there won't be any duplicate books
j a
1,935 PointsINTERSECT returns only values that are in both data sets.So the answer can't be "Intersect".
Mark Chesney
11,747 Points@Steven:
I believe that's the same answer (other than table aliases) that was already proposed by Jason on Oct 12.
Yes, I agree with you, but since Jason didn't submit his answer as an answer, I had to upvote Krishna's answer :) (It's the one feature I dislike about the otherwise fantastic UI in these Treehouse Community threads) :)
Mark Chesney
11,747 PointsHowever, Marco, I believe your solution does answer question/query #3 of 3:
-- Create a list of duplicate books.
-- Book titles that exist in BOTH north AND south locations
Jason Anello
Courses Plus Student 94,610 PointsJason Anello
Courses Plus Student 94,610 Pointsfixed code formatting