[The Wall of Learning SQL] Conquer JOINs! The Difference Between INNER JOIN and LEFT JOIN Explained
"I have an employee list, but the department names are in another file... I want to see a list with both the employee names and department names together!"
"I want a list that shows which product belongs to which category at a glance!"
As a web creator working with databases, one challenge you'll almost certainly face is wanting to "see information scattered across multiple tables consolidated into one view." Customer information and purchase history, product masters and inventory tables—in a normalized database, it's common for information to be divided into tables by purpose.
The powerful SQL weapon that connects this scattered information, just like fitting puzzle pieces together, is the "JOIN". If you can master JOINs, the range of data you can handle with SQL will expand explosively, allowing you to extract more complex and valuable information.
In this article, we'll focus on two of the most frequently used JOINs, INNER JOIN and LEFT JOIN, and thoroughly explain their roles and how to choose between them with diagrams and copy-paste-ready code that even beginners can absolutely understand!
Preparation: Tables for Learning JOINs
To clearly understand how JOINs work, we'll once again use an "employees" table and a "departments" table. This time, to make the differences between the JOINs easy to see, we've specifically included data for "a new trainee not yet assigned to a department" and "a newly established PR department with no employees."
Please copy the SQL statement below and run it in your own database environment, or try it in the "SQL Execution Environment" described later.
-- Drop tables if they exist
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
-- Create departments table
CREATE TABLE departments (
id INT PRIMARY KEY,
department_name VARCHAR(50)
);
-- Insert department data
INSERT INTO departments (id, department_name) VALUES
(1, '営業部'),
(2, '開発部'),
(3, '人事部'),
(4, '広報部'); -- A department with no employees yet
-- Create employees table
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT -- Allow NULLs
);
-- Insert employee data
INSERT INTO employees (id, name, department_id) VALUES
(1, '山田 太郎', 1),
(2, '鈴木 花子', 2),
(3, '佐藤 次郎', 1),
(4, '高橋 三郎', 3),
(5, '田中 恵子', 2),
(6, '中村 さくら', NULL); -- A new trainee not yet assigned to a department
Ready? Let's embark on a journey to connect these two tables!
1. INNER JOIN: Joining Only the Common Parts of Two Tables
INNER JOIN is the most basic and most commonly used JOIN. In a nutshell, it's a join method for "extracting only the related data that exists in both tables."
If you visualize it with a Venn diagram, it's like taking only the "intersection" where the two circles overlap. In other words, you'd use it in cases where you only want information about "employees who belong to a department."
Basic Usage
You specify the first table in the FROM clause and the second table with INNER JOIN. Then, in the ON clause, you define which column to use as the key to link the tables together.
SELECT
e.name,
d.department_name
FROM
employees AS e
INNER JOIN
departments AS d ON e.department_id = d.id;
Take a look at this result. There are two important points:
- 'Nakamura Sakura', who is not assigned to a department, is not included in the results.
- The 'PR Department' (広報部), which has no employees, is also not included.
This is the main characteristic of an INNER JOIN. Only data that satisfies the join condition ON e.department_id = d.id—meaning data that exists in both tables—is returned.
※ By defining an alias after the table name, like employees AS e, you can write shorter and more convenient queries!
2. LEFT JOIN: Displaying All Records from One Table
A LEFT JOIN is a join method that "displays all data from the left table (the first one specified in the FROM clause) and attaches the related data from the right table." It's also called a LEFT OUTER JOIN, but the `OUTER` keyword is optional.
In a Venn diagram, this is like including the entire left circle, and only the overlapping part of the right circle. If there's no corresponding data in the right table, that part will contain NULL (empty).
Example 1: Showing All Data Based on "Employees"
Let's consider the case where "I want a full list of employees. If they belong to a department, I also want to know the department name." In this case, we place the "employees" table, which is our base, on the left side.
SELECT
e.name,
d.department_name
FROM
employees AS e
LEFT JOIN
departments AS d ON e.department_id = d.id;
Pay attention to the result! Unlike with the INNER JOIN, 'Nakamura Sakura', who has no assigned department, is included in the result. And her department_name is NULL.
This is the power of LEFT JOIN. Since all data from the left table (employees) is displayed, it's perfect for tasks like "finding employees who are not yet assigned to a department."
Example 2: Showing All Data Based on "Departments"
So, what happens if we swap the left and right tables? Let's consider the case where "I want to see a list of all departments. If there are any employees in them, I want to know their names too." This time, let's put the "departments" table on the left.
SELECT
d.department_name,
e.name
FROM
departments AS d
LEFT JOIN
employees AS e ON d.id = e.department_id;
The result is different again! The 'PR Department' (広報部), which has no employees, is now properly displayed, and the corresponding employee name (name) is NULL. As you can see, with a LEFT JOIN, which table you place on the "left" side is extremely important.
Key Points for Choosing Between INNER JOIN vs. LEFT JOIN
If you're ever stuck on which one to use, just think about what you want the "main character" of your data to be.
-
INNER JOIN: "I only want to see data where both employee and department information exists!"
→ Use when you want to see the strict relationship between two tables. -
LEFT JOIN: "I want to see all employees. Department information is nice to have, but optional!"
→ Use when you want to display all records from one "master" table as your base.
If you think based on this criteria, you'll naturally be able to decide which JOIN is appropriate.
Practice! Let's Try JOINs in Your Browser
Thanks for waiting! Here is an environment where you can get hands-on and try out the INNER JOIN and LEFT JOIN you've learned so far.
Copy the entire code block below, save it as a file named something like join_practice.html, and open it in your browser. Try swapping the left and right tables, or switching between INNER and LEFT, to see how the results change!
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>SQL JOIN Playground</title>
<script src="https://cdn.jsdelivr.net/npm/alasql@4"></script>
<style>
body { font-family: sans-serif; padding: 2rem; background-color: #f9f9f9; }
.container { max-width: 800px; margin: auto; background: white; padding: 2rem; border-radius: 8px; box-shadow: 0 4px 8px rgba(0,0,0,0.1); }
h1 { color: #333; }
textarea { width: 100%; height: 180px; font-family: monospace; font-size: 16px; padding: 10px; border: 1px solid #ccc; border-radius: 4px; box-sizing: border-box; margin-bottom: 1rem; }
button { background-color: #007bff; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; font-size: 16px; }
button:hover { background-color: #0056b3; }
#result-area { margin-top: 2rem; }
table { width: 100%; border-collapse: collapse; margin-top: 1rem; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
.error { color: red; font-weight: bold; }
</style>
</head>
<body>
<div class="container">
<h1>Let's Try JOINs!</h1>
<p>Enter an SQL statement in the text area below and press the "Execute SQL" button.</p>
<textarea id="sql-input">-- Write your SQL in here
SELECT
e.name,
d.department_name
FROM
employees AS e
LEFT JOIN
departments AS d ON e.department_id = d.id;</textarea>
<button onclick="executeSQL()">Execute SQL</button>
<div id="result-area"></div>
</div>
<script>
// Initialize database and prepare data
const db = new alasql.Database();
db.exec(`
CREATE TABLE departments (id INT, department_name STRING);
INSERT INTO departments VALUES (1, '営業部'), (2, '開発部'), (3, '人事部'), (4, '広報部');
CREATE TABLE employees (id INT, name STRING, department_id INT);
INSERT INTO employees VALUES (1, '山田 太郎', 1), (2, '鈴木 花子', 2), (3, '佐藤 次郎', 1), (4, '高橋 三郎', 3), (5, '田中 恵子', 2), (6, '中村 さくら', NULL);
`);
function executeSQL() {
const sql = document.getElementById('sql-input').value;
const resultArea = document.getElementById('result-area');
resultArea.innerHTML = '';
try {
const result = db.exec(sql);
if (result.length > 0) {
resultArea.appendChild(createTable(result));
} else {
resultArea.innerHTML = '<p>The query returned 0 rows.</p>';
}
} catch (e) {
resultArea.innerHTML = `<p class="error">Error: ${e.message}</p>`;
}
}
function createTable(data) {
const table = document.createElement('table');
const thead = table.createTHead();
const tbody = table.createTBody();
const headerRow = thead.insertRow();
for (const key in data[0]) {
const th = document.createElement('th');
th.textContent = key;
headerRow.appendChild(th);
}
data.forEach(rowData => {
const row = tbody.insertRow();
for (const key in rowData) {
const cell = row.insertCell();
cell.textContent = (rowData[key] === null || rowData[key] === undefined) ? 'NULL' : rowData[key];
}
});
return table;
}
// Initial display
executeSQL();
</script>
</body>
</html>
Points to Watch Out For and Related Techniques
The Difference Between ON and WHERE
Beginners often get confused about when to use ON versus WHERE. Let's remember it simply:
- ON: Defines the joining rule—"which key to use" to connect the tables.
- WHERE: Defines the filtering rule—"which rows to narrow down" from the large, combined table that the join created.
Think of it as a flow: first, you correctly connect the tables with ON, and then you filter only the necessary data with WHERE.
RIGHT JOIN and FULL OUTER JOIN
JOIN has other family members too.
- RIGHT JOIN: This is the reverse version of
LEFT JOIN. It displays all records from the right-side table as its base. - FULL OUTER JOIN: This displays all data from both the left and right tables, inserting
NULLwhere there is no corresponding data.
In practice, you'll use LEFT JOIN overwhelmingly more often, but it's good to keep in the back of your mind that these other members exist.
Summary
Great work! We've explained the differences and use cases for INNER JOIN and LEFT JOIN, the fundamentals of table joining.
- INNER JOIN: Use when you only want the common parts of two tables. Unrelated data is excluded from the result.
- LEFT JOIN: Use when you want to display all data with one table as the main character. Missing related data is supplemented with
NULL. - The choice of which JOIN to use is decided by "What do I want as the main character of my data?"
JOIN is a core feature for unlocking the true potential of relational databases. It might feel a bit difficult at first, but the more you use it, the more you'll understand its convenience, and the more fun extracting data will become. Please try out various patterns and make JOIN your new weapon!