SQL #6: OUTER JOINS & NULLS

@grchab

#sql #learnsql #sqltips #sqltutorial #learntocode #learnontiktok #learnwithtiktok #learninpublic

♬ original sound – Curiosity Corner 🙇🏽‍♂️

So last post we used JOINs to look at information from 2 tables. There are different types of JOINs.

We got a ‘buildings’ table with 2 columns: building_name and capacity. And an ’employees’ table with 4 columns: role, name, building, and years_employed. The task is to list all buildings and the distinct employee roles in each building (including empty buildings).

SELECT DISTINCT building_name, role 
FROM buildings 
  LEFT JOIN employees
    ON building_name = building;

This is the solution code so let’s walk through it. First, we want to see the buildings, and employee roles, so we select those 2 columns. DISTINCT is what stops duplicate elements from being shown. Each of those are from different tables, so we first take ‘building_name’ FROM ‘buildings’, then we LEFT JOIN the ’employees’ table for the roles.

In the ‘buildings’ table, there are 4 total buildings, 2 with employees, and 2 without. LEFT JOIN allows the buildings without employees to still be present in the final table. INNER JOIN, (which is the same as JOIN by itself), would only show the buildings with employees. There’s also a RIGHT, a FULL, and a CROSS JOIN, which has differences but the system wouldn’t show them for this task, so we’ll get to those later.

The last line with the ‘ON’ is what commands the system to match the two columns from the 2 different tables. Without it, it would take those empty buildings and match it with roles that aren’t even in those buildings, it’s a syntax error. I don’t fully understand it either, I’ll have to come back.

NULL

Like anything else, null means nothing. Still using the employees and buildings tables, let’s say we just hired some new employees who haven’t assigned to a building yet. To view those employees, I’d do:

SELECT name, role FROM employees
WHERE building IS NULL;

We’re only using the ’employees’ table for this one, who has a ‘building’ column. So since these employees don’t have a building, theirs would be NULL.

Now vice versa, if I wanted to query the buildings who don’t have employees, the command wouldn’t be as simple.

I’d have to do:

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

The LEFT JOIN line and down is where the bulk of the work is done. We’re using LEFT JOIN to not only select both tables, but to view the rows where their building columns, ‘building_name’ for the ‘buildings’ table and ‘building’ for the employees table, are matched to view their respective instances. And of course since we’re looking for buildings who don’t have employees, we NULL the ‘role’ column.

What I’m liking about SQL so far is I can experiment with different commands, so even if I don’t understand why one works, I can at least see that it works.