SQLBolt.com – Alternative Answer to Lesson 8, Q2

I have an alternative answer to the 2nd question from Lesson 8 from https://sqlbolt.com/lesson/select_queries_with_nulls

Find the names of the buildings that hold no employees

My attempt is: SELECT building_name FROM buildings WHERE building_name NOT IN(SELECT DISTINCT(building) FROM employees);

It’s not returning my answer as correct and prefers the answer SELECT building_name FROM buildings LEFT JOIN employees ON building_name = building WHERE name IS NULL

Please tell me if I’ve made a mistake. I didn’t think that a join was needed here. Thanks

Answer

You have not made a “mistake”. But you query still has two issues.

First, select distinct is totally unnecessary. IN/NOT IN take care of that — they ignore duplicates.

More importantly, NOT IN does not work as most people expect with NULL values — if any value in the subquery is NULL then no results are returned at all. If the employees table has no NULL values in building then your version should work as intended.

For this reason, I strongly recommend that you always use NOT EXISTS with a subquery:

SELECT b.building_name
FROM buildings b
WHERE NOT EXISTS (SELECT 1
                  FROM employees e
                  WHERE b.building_name = e.building
                 );

The LEFT JOIN version is functionally equivalent to this version.