This question comes up occasionally, and I usually end up being a tad evasive about it. So, it comes time to post the real answer here:
“Lets say you have a student table and teacher table. How do you write a SQL query to show all teachers that don’t have a student?”
I always say, “Well the first thing that comes to mind is
SELECT t.teacher_id FROM teachers t WHERE t.teacher_id NOT IN (SELECT s.teacher_id FROM students s)
but I know there is a more performant method.” Then I don’t follow up with the answer.
Yeah, I know…
The above query basically works, but it is slow because it has to pull up a list of all student records, extract the teacher_id, and then take the list and apply it to the teacher table. It is a very slow approach despite being VERY clear to the maintenance programmer.
So, lets write up the more performant answer now!
SELECT t.teacher_id FROM teachers t LEFT JOIN students s ON s.teacher_id = t.teacher_id WHERE s.teacher_id IS NULL
Now the query says “OK, lets do a join and just throw out the places where there is no corresponding teacher_id” It is MUCH, MUCH quicker because it runs as a “single query”, instead of basically breaking down into 2 queries.
However, I would argue that if you were writing SQL for testing purposes, and not production purposes, you would want to use the first of the two examples.
Yeah, I know….
There is a real reason for it. The average QA person, even one who specializes in automation, has very different goals than the average developer. A developer wants to write the fastest SQL query possible because the end user wants their answer and the query may be run hundreds of times a second. On the other hand, a QA Engineer wants the answer, and has a bit more luxury in their time. Furthermore, if the QA is writing code for automation, then their primary goal is correct functionality and maintainability. The QA may need to use the faster solution, but the greater concern is clarity.
Got a better answer than me? Leave a comment to flame me below! I welcome the knowledge!