Databases Querying Relational Databases Set Operations Review and Practice

Task 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!

fixed code formatting

How would you get it so both tables link into one?

8 Answers

Alexander Nikiforov
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Alexander Nikiforov
Java Web Development Techdegree Graduate 22,150 Points

I 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

Hi 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
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Alexander Nikiforov
Java Web Development Techdegree Graduate 22,150 Points

Jason Anello

You 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 :)

I 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
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Alexander Nikiforov
Java Web Development Techdegree Graduate 22,150 Points

Thanks 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
Allan Oloo
8,054 Points

really helpful thanks!

Alan McClenaghan
Alan McClenaghan
Front End Web Development Techdegree Graduate 45,533 Points

It'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
Steven Parker
201,933 Points

The solution I posted doesn't require anything that had not been covered yet.

Steven Parker
Steven Parker
201,933 Points

This answer is a bit late :smirk:, but you can do this with derived tables:

-- 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
Giuseppe Ardito
14,130 Points

Given that the exercise wanted us to do that, I find this to be the most elegant solution.

The only issue though is that we don't learn about derived tables until the next section.

Giuseppe Ardito
Giuseppe Ardito
14,130 Points

Yes, 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?

Hi 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
Giuseppe Ardito
14,130 Points

Great. This clarifies! Thanks a lot. Do you know in what documentation we can actually see with what priority these operations are processed?

I 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.

Dan Coleman
Dan Coleman
2,292 Points

Have 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
Christian Scherer
2,989 Points

I had the same feeling, somehow the questions seem kinda ambiguous. Could one of the teachers clarify?

Krishna Guggilla
Krishna Guggilla
1,467 Points

This 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
Steven Parker
201,933 Points

I believe that's the same answer (other than table aliases) that was already proposed by Jason on Oct 12.

The one thing I'm sure of is that there are a few different ways of solving problems with SQL too... :smile:

Patriot Rika
Patriot Rika
3,738 Points

I 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
Gregory Ledger
6,116 Points

Thanks 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
Kareem Jeiroudi
14,982 Points

Have 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>";

I 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

INTERSECT returns only values that are in both data sets.So the answer can't be "Intersect".

@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) :)

However, 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