SQL INNER JOIN vs LEFT JOIN Explained

Which rows survive

Our data has one employee without a department (Zoya) and one department without employees (Finance). An inner join drops both. A left join from employees keeps Zoya with a NULL department name; a left join from departments keeps Finance with no employees.

Which table is "left" matters: it is the one written first, after FROM. Put the table whose rows you must keep on the left.

Counting with a LEFT JOIN

When a left row has no match, the joined row still exists, with NULLs on the right. COUNT(*) counts that row as 1; COUNT(e.emp_id) skips NULLs and correctly gives 0.

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

Examples

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

SELECT d.dept_name, COUNT(e.emp_id) AS employees
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY employees DESC, d.dept_name;

Common mistakes

• Filtering the right table in WHERE. LEFT JOIN … WHERE d.dept_name = 'Sales' removes the NULL rows, turning it into an inner join. Put such conditions in the ON clause instead.
• COUNT(*) after a LEFT JOIN. It counts unmatched rows as 1. Count a column from the right table.
• Join fan-out. Joining to a table with several matches per row multiplies rows, so sums and counts grow. Aggregate first in a subquery or CTE, then join.

Practise INNER JOIN vs LEFT JOIN

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

1. 1

   Users With Zero Searches

   Find registered account profiles that have never submitted a search query using an outer join.

   Easy✓ Solved
2. 2

   Ad Click-Through Rate (CTR)

   Calculate the percentage CTR per ad campaign rounded to 2 decimals, safely handling campaigns with zero impressions.

   Easy✓ Solved
3. 3

   Google Maps: High-Impact Local Guides

   Find verified reviewers who submitted at least 2 photo reviews in San Francisco or Mountain View.

   Easy✓ Solved
4. 4

   Google Drive: External Sensitive File Shares

   Audit sensitive files shared with non-Google email addresses to identify potential data exposure risks.

   Easy✓ Solved
5. 5

   Employees Earning More Than Their Managers

   Join the employee table with itself to find staff members whose salary exceeds their direct manager.

   Easy✓ Solved
6. 6

   Customers Who Never Order

   Identify all customer records that have no matching entries in the Orders table.

   Easy✓ Solved

Want a full set? Work through the SQL Joins study plan.

Questions people ask

Is LEFT JOIN the same as LEFT OUTER JOIN?

Yes. OUTER is optional; both mean the same thing in MySQL and PostgreSQL.

Does MySQL support FULL OUTER JOIN?

No. PostgreSQL does; in MySQL you combine a LEFT JOIN and a RIGHT JOIN with UNION.

Related concepts

• SQL Self Join Explained: Joining a Table to Itself
• SQL NULL Handling and COALESCE Explained
• SQL Subqueries Explained: Scalar, Correlated and EXISTS