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

mykolash
mykolash
12,955 Points
Posted by mykolash
mykolash
12,955 Points

Why can't we use two embedded SELECT + WHERE + IN requests instead of two JOINs as teacher Ben Deitch did?

I've solved the task in a bit different way. Can smbd say which way is better - joining tables or using SELECT request multiple times?

-- Which teachers teach only students in 8th grade?
SELECT Teachers.first_name, Teachers.last_name FROM TEACHERS WHERE Teachers.id IN
  (
  SELECT Classes.teacher_id FROM CLASSES WHERE Classes.subject_id IN
    (
      SELECT id FROM Subjects WHERE grade=8
    )
  );

Ben Deitch

And thanx in advance.

PS: Oops. here is link to the lesson

1 Answer

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

Your alternative does a good job of providing the same result set. And by filtering using IN, you eliminated the need for the DISTINCT restriction.

But the additional filters and sub-queries might cause this query to be a bit less efficient than the original. Benchmark testing might be needed to be certain.

The original may also be easier to read and comprehend, but that could be a matter of personal preference.

But as far as getting the job done, this is clearly an equally valid solution. :+1:

mykolash
mykolash
12,955 Points
mykolash
mykolash
12,955 Points

Thanx a lot, Steven Parker

I've used multiple-SELECTs with such a logic. Using JOINs we add more and more data to the request and create bigger JOINed table and filtering it after everything is added. While using multiple-SELECTs we request separated tables one by one, prolonging request in time, but decreasing processor calculations at each moment. What, I think/hope will allow to provide requests on other threads. Not sure, I've explained it in clear manner :-(

Anyway, thanx for your answer!

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

As I said, it might take benchmark testing to be sure, but the database may quite likely perform optimizations that would discard columns not used in the final SELECT. I would expect sub-queries and filters to be more costly in terms of performance than JOINs.