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
D Elis
13,571 PointsQuestion about SQL - Review and Practice from Querying Relational Databases …
I am trying to solve the question below but I am having a little trouble to figure out why my solution is only returning one book instead of returning all books that match in both tables.
My question: In which cases can we use WHERE IN to answer this kind of question instead of a JOIN or a UNION or an INTERSECT to do the same thing?
The Challenge: -- Generate a report that lists the book titles from both locations and count the total number of books with the same title.
SELECT title, COUNT(*) AS num FROM books_north WHERE title IN (SELECT title FROM books_south WHERE books_north.title=books_south.title)
The correct answer:
SELECT title, COUNT(*) AS num FROM (SELECT title FROM books_north UNION ALL SELECT title FROM books_south) GROUP BY title;
If someone can help me to better understand when to use which Keyword (WHERE IN, JOIN or INTERSECT) to solve this question I would appreciate the feedback!
2 Answers
D Elis
13,571 Pointsthank you
Mark Chesney
11,747 PointsWow, how challenging! I notice the video and cheatsheet notes both talk about 2 main ways subqueries are done:
- In an
INcondition - * e.g.:
SELECT <columns> FROM <table 1> WHERE <table 1>.<column> IN (<subquery>); - As a derived or temporary table
And yet, the answer up above (which totally works), as well as this other answer, all seem to be a third case, which is
SELECT <columns> FROM (subquery)
I hope I'm not the only one puzzled by it!
Anne-Marie Hofmann
1,776 PointsI also was puzzled. I felt like a UNION was needed, but they didn't discuss UNION at all in the video, so I found it difficult to attempt to figure this one out. I basically came searching for the answer, so that I could attempt to teach myself the concept based off what I've learned. I'm not sure I have the confidence I'll figure this one out in future scenarios, though.
Cara Marco
2,603 PointsCara Marco
2,603 PointsHi D Elis,
I don't know if this is exactly what you're looking for, but here's my brief take on the uses of each keyword:
1) WHERE IN - Allows you to retrieve records in which the value of the column can be any one of several different values. For example, if you were to use WHERE IN to find particular books from this library, you might use:
SELECT title FROM books_north WHERE title IN ("It", "The Shining", "The Green Mile");
2) JOIN - Ignoring, for the moment, the different types of joins, a join combines two or more tables so that you can use columns from both tables in your result set. Say you have a PATRONS table (patronid, name, email) and a CHECKOUTS table (title, date, patronid) you can crash them together so that you can use some information from each table to get a list of patron checkouts:
SELECT name, email, title, date FROM patrons JOIN checkouts ON patrons.patronid = checkouts.patronid;
3) UNION ALL - UNION ALL combines the values from two or more tables into a single result set including duplicates. If you'd used just UNION in the query, you would have eliminated cases where the same book was at both library branches, so UNION ALL was the right keyword to use.
4) INTERSECT - INTERSECT returns only the values that are common to both tables. So for example, if you replaced UNION ALL with INTERSECT in the correct query above, you'd only retrieve titles where there was a copy of the book at both branches.
Hope that helps.