If you’ve worked with relational databases, you’ve probably come across situations where you need to get related data from more than one table. For example, maybe you have an employees table and a departments table, and you want to know which employee belongs to which department.
This is where SQL JOINs come in.
JOINs are a way to bring data together using a column that both tables share — usually an ID or some kind of unique value. Once you understand how JOINs work, writing more powerful queries becomes much easier.
In this post, we’ll look at the four most commonly used types of SQL JOINs:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
And we’ll go through each one with clear, real-world examples.
What Is a SQL JOIN?
A JOIN in SQL is used to combine rows from two or more tables based on a related column — often something like id, user_id, or department_id.
For instance, if two tables both have a department_id, a JOIN can use that to combine information from both tables into a single result.
1. INNER JOIN — Matching Data Only
The INNER JOIN is probably the most used. It only includes rows where there is a match in both tables. If a row exists in one table but not the other, it won’t appear in the results.
SQL Syntax:
SELECT table1.column, table2.column
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;Example:
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;This will only show employees who are assigned to a department. If an employee doesn’t have a department, they won’t show up in the results.
2. LEFT JOIN — Everything from the Left Table
The LEFT JOIN (also called LEFT OUTER JOIN) shows all rows from the left table. If there’s a matching row in the right table, it adds that too. But if there’s no match, the right side will just show NULL.
SQL Syntax:
SELECT table1.column, table2.column
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;Example:
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
This one shows all employees. If an employee isn’t assigned to a department, they’ll still appear, but the department column will be empty (or NULL).
3. RIGHT JOIN — Everything from the Right Table
The RIGHT JOIN is the reverse of LEFT JOIN. It keeps all rows from the right table and includes matches from the left table where they exist. If there’s no match, the left side will be NULL.
SQL Syntax:
SELECT table1.column, table2.column
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;Example:
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;This shows all departments. Even if a department doesn’t have any employees assigned to it, it will still appear in the results. Employees from other departments are matched in when possible.
4. FULL OUTER JOIN — Everything from Both Sides
A FULL OUTER JOIN includes all rows from both tables. Where there is a match, it shows the data combined. Where there is no match, the missing side is filled in with NULL.
SQL Syntax:
SELECT table1.column, table2.column
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;
Example:
sqlCopyEditSELECT employees.name, departments.department_name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.id;This one gives you the full picture — all employees and all departments. If a department has no employees, it’s still shown. And if an employee isn’t assigned to any department, they’re shown too.
Note: Not all SQL databases support FULL OUTER JOIN. MySQL, for example, doesn’t support it directly. But you can work around that using a
UNIONof a LEFT JOIN and a RIGHT JOIN.
Why Are JOINs Useful?
JOINs are helpful whenever your data is spread across multiple tables — which is usually the case in a well-designed relational database.
Here are a few common reasons to use them:
- Fetch related data in one query
- Create reports and dashboards
- Avoid duplicated data by using normalized tables
- Understand how different parts of your data relate to each other
If you’re working with more than one table, JOINs are usually the way to bring everything together.
JOIN Summary Table
| JOIN Type | Includes Rows From | What Happens When There’s No Match |
|---|---|---|
| INNER JOIN | Both tables (only matches) | Row is excluded |
| LEFT JOIN | Left table | NULL values from right table |
| RIGHT JOIN | Right table | NULL values from left table |
| FULL OUTER JOIN | Both tables | NULL where there’s no match |
Final Thoughts
JOINs might seem confusing at first, especially when you’re not sure which type to use. But once you get used to how they behave, they become one of the most useful tools in SQL.
If you’re just starting out, try writing small queries with sample data. Create a couple of simple tables — like students and courses — and experiment with JOINs. See what happens when rows match and when they don’t.
And most importantly, always double-check your results, especially when working with LEFT or FULL JOINs. It’s easy to overlook rows with NULLs, but those often tell you something important.
Understanding JOINs isn’t about memorizing syntax — it’s about knowing what kind of result you want and choosing the JOIN that gives it to you.