kevin hudsonPro Student 9,652 Points
Is it a bad approach to use subqueries over CTE and JOINING?
I'm finding out that I can understand joining data through subqueries so much better than understanding CTE and Joining. I understand there is a right time to use other methods but if it produces the same result with less code it should be ok?
-- Which teacher teaches 7th grade science? SELECT DISTINCT ID, FIRST_NAME, LAST_NAME FROM TEACHERS WHERE ID IN(SELECT TEACHER_ID FROM CLASSES WHERE SUBJECT_ID IN(SELECT ID FROM SUBJECTS WHERE NAME LIKE "%scien%" AND GRADE = 7) );
Steven Parker201,347 Points
When you start benchmarking query efficiencies, you may discover reasons to choose one method over another, but for now if it works and is readable/maintainable, it should be essentially your choice.
One extra bonus of your approach is that you don't need that "DISTINCT" qualifier.
SELECT DISTINCT( FIRST_NAME ||" "|| LAST_NAME) AS FULL_NAME FROM TEACHERS AS t INNER JOIN CLASSES AS c ON t.ID = c.TEACHER_ID WHERE c.SUBJECT_ID IN (SELECT s.ID FROM SUBJECTS AS s WHERE GRADE = "7" AND NAME = "Science")