April 29, 2024

kalin dee

A Better Way Of Life

Why and how do these two queries both work

Why and how do these two queries both work

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