Why and how do these two queries both work

Question

I have been trying to learn SQL using SQLBolts tutorial and for this exercise, I needed to write a query that showed the names of all the buildings with no employees using only LEFT JOIN. I had an answer different from the website and I am wondering why they both work. The only difference between our solutions is I put WHERE buildings is NULL vs SQL Bolts solution of WHERE role is null. If building value is null shouldn't that return a null value? Also same with role how does the Database know a building has a null value for role when there isn't a building attached to that role?

my query

SELECT building_name                                     
FROM buildings                                                  
LEFT JOIN EMPLOYEES                                        
ON buildings.building_name = EMPLOYEES.building       
WHERE building IS NULL

SQL BOLT query

SELECT DISTINCT building_name, role                              
FROM buildings                                                  
LEFT JOIN employees                                               
ON building_name = building                                     
WHERE Role IS NULL

Buildings (table1) and Employees (table2)

Null Values from Database

Basically, anything from the table on the right that doesn't match with `ON buildings.building_name = EMPLOYEES.building` will return `NULL`. So if you define any column from the right table `WHERE right_table.column_name IS NULL`, you'll get the same result. You should learn how `LEFT JOIN` works then only you'll understand how that happen. — FanoFN, Dec 28 '21 at 05:01
@FanoFN: not *any* column, you should avoid using a column that might actually have a null value — ysth, Dec 28 '21 at 06:22
Oh yeah, that's right @ysth. My bad, I probably was too focused at the `ON` condition that the possibility of other columns data is `NULL` slips my mind. — FanoFN, Dec 28 '21 at 06:25

Akina · Answer 1 · 2021-12-28T05:10:18.057

Formally both queries are incorrect until complete tables structures are defined. When the query datasource includes more than one table copy then each column name must be specified with its table alias part. Exclusion - the column names used in USING clause or common columns when NATURAL JOIN is used, these columns may be used without table aliases. Backward exclusion - the query is used in compound statement, and local variables are present, in this case aliases must be used unconditionally.

If the tables structures claims that the queries texts are correct (all columns which have no table aliases are unique) then your query is more correct because it uses the column used in JOIN condition while testing for NULL. The "SQL BOLT query"may give wrong result if Role column is nullable and some rows contains NULL in this column.

If Role is defined as NOT NULL (directly or indirectly - for example, by according CHECK constraint, or it is a part of primary key) then both queries will give the same output.

Why and how do these two queries both work

1 Answers1