Simple explanation for inner join and left join

Dec 25, 2011 by chatchai

Summary: The inner join is for filtering out unwanted records and left join is for pulling in extra information

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 a function to get list of employees in the database shown below

The business logic for this method is that we are only interested in the employees that have been assigned to a project, the SQL string is shown below

SELECT employees.name, projects.name as project
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_role
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