Last week, I have heard my colleague explained how inner join and left join are used. SQL join types are typically explained using Venn diagrams but my colleague’s explanation is from the view of how we are using them.

Let’s say we are writing an SQL to get list of employees in the database shown below

SELECT employees.name, 
    projects.name as projectFROM 
FROM employees 
INNER JOIN projects 
ON employees.project_id = projects.project_id

You can see in the result set that we uses inner join for filtering out unwanted records which are those employee records that don’t have corresponding project and those projects that haven’t been associated to an employee

Now we also want to know whether each employee in the result set has access right to the laboratory room. We uses left join to pulling in extra information. Our SQL will looks like:

SELECT employees.name, 
    projects.name AS project , 
    lab_accesses.role AS lab_roleFROM 
FROM employees 
JOIN projects ON employees.project_id = projects.project_id 
LEFT JOIN lab_accesses ON employees.lab_code = lab_accesses.lab_code

Our main purpose is to get list of employees with corresponding projects. The lab_role value is just our extra information. It is fine if an employee doesn’t have laboratory access right, we still want to include the employee in our result set. That’s why we use left join

It is somewhat oversimplified but I found this explanation is quite easy to remember