LEFT JOIN vs. LEFT INNER JOIN in SQL
Compare LEFT JOIN vs. LEFT INNER JOIN in PostgreSQL, fix invalid syntax, and check which employee rows LEFT JOIN and INNER JOIN keep with a runnable example.

LEFT INNER JOIN isn't valid PostgreSQL syntax: choose LEFT JOIN or INNER JOIN instead. LEFT JOIN keeps every left-side row, including employees without a matching department, while INNER JOIN returns only matching combinations. LEFT OUTER JOIN means the same thing as LEFT JOIN.
The valid join choices
Choose LEFT JOIN when employees without a department match must stay in your report, and choose INNER JOIN when the report should show only employees with a department match. The choice starts with the reporting requirement, not with which join name sounds more specific.
For example, an employee assignment review should usually preserve employees whose department records are missing. Both queries can run successfully, but only the join that matches the requirement gives the intended result.
- LEFT JOIN preserves every row from the left input and adds matching right-side values.
- INNER JOIN returns combinations that satisfy the matching condition.
- LEFT OUTER JOIN is another spelling of LEFT JOIN.
- LEFT INNER JOIN combines incompatible join modifiers and produces a PostgreSQL syntax error.
Join names and matching rules
The join name controls whether unmatched rows survive, while the ON condition defines which rows match. Keeping those responsibilities separate makes join queries easier to write and debug.
The left input is the table expression before the join. In the exercise below, employees is the left input and departments is the right input. The matching condition compares each employee's department_id with a department's department_id.
When a LEFT JOIN finds no department match, PostgreSQL supplies NULL for the department columns in that output row. NULL is a marker for a missing or unknown value. NULL isn't an empty string or zero.
The naming rules explain the confusing phrase. INNER is optional in an ordinary JOIN, so JOIN with an ON condition means INNER JOIN. OUTER is optional in LEFT OUTER JOIN, so LEFT JOIN means LEFT OUTER JOIN. LEFT and INNER aren't interchangeable modifiers that you can stack together.
All queries in this article use PostgreSQL. Use PostgreSQL's documentation page titled Table Expressions when you need to check the join grammar rather than guessing from similar-looking keywords.
Practice what this post teaches
A syntax-repair exercise
Run the starting query, then change only the join keywords as instructed to separate a syntax error from a valid query that returns different rows. The query uses common table expressions, which are named temporary results within one statement, so you don't need to create permanent tables.
Paste the complete starting query into a PostgreSQL query editor. WITH introduces the named results, VALUES supplies the fictional rows, and the aliases e and d provide short names for the two inputs. ORDER BY keeps the displayed employees in a predictable order.
-- Fictional PostgreSQL exercise
-- Goal: choose whether employees without a department match stay.
-- Starting employees: (1, 'Mira', 10), (2, 'Owen', 20).
-- Starting departments: (10, 'Support').
-- Match rule: employee department_id equals department department_id.
-- Model: Mira has one match; Owen has no match.
-- Step 1: Run the starting query.
WITH employees (employee_id, employee_name, department_id) AS (
VALUES (1, 'Mira', 10), (2, 'Owen', 20)
),
departments (department_id, department_name) AS (
VALUES (10, 'Support')
)
SELECT e.employee_name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
ORDER BY e.employee_id;
-- Expected rows, with missing values written as NULL:
-- employee_name | department_name
-- Mira | Support
-- Owen | NULL
-- Model: one matched row plus one unmatched left row = two rows.
-- Step 2: In the query above, replace LEFT JOIN with LEFT INNER JOIN.
-- Run the edited query.
-- Expected outcome: a syntax error, not an empty result.
-- Step 3: Replace LEFT INNER JOIN with INNER JOIN.
-- Run the edited query.
-- Expected rows:
-- employee_name | department_name
-- Mira | Support
-- Model: only the one matched row survives.
-- Step 4: Replace INNER JOIN with LEFT OUTER JOIN.
-- Run the edited query.
-- Expected rows:
-- employee_name | department_name
-- Mira | Support
-- Owen | NULL
-- Model: LEFT OUTER JOIN restores the original LEFT JOIN result.
In step one, Mira matches Support because both records use department 10. Owen references department 20, which doesn't exist in the department input. LEFT JOIN preserves Owen and supplies NULL for department_name. Your database client may display that NULL as a blank cell or another marker; the value hasn't become an empty string.
In step two, PostgreSQL rejects the edited statement because LEFT INNER JOIN doesn't fit its grammar. A parser is the part of the database that checks a statement's structure. A parser error means the database couldn't interpret the query as written, not that the query found no matching records.
In step three, INNER JOIN is valid, so the query runs. Owen disappears because Owen has no matching department, which is the intended behavior of INNER JOIN. In step four, LEFT OUTER JOIN restores Owen because OUTER doesn't change the meaning of LEFT JOIN.
Try the matching-only version independently with INNER JOIN: combine matching rows, then compare the result with the employee example.
Result checks and limits
Check employee identities as well as row counts, because a successful query doesn't prove that the chosen join meets the reporting requirement. In the exercise, Owen's presence or absence tells you whether unmatched employees survived.
Keep the ON condition unchanged while comparing the join names. Changing both the join type and the matching condition makes the result harder to interpret because either edit could explain the difference.
The exercise has an important limitation: the department identifiers are unique. If several right-side records match one employee, the join can return several rows for that employee. LEFT JOIN preserves left-side rows, but it doesn't guarantee exactly one output row per employee.
Don't choose a join type based on an assumed speed advantage. Choose the required rows first. If a real query is slow, investigate its execution plan, the database's description of how it will run the query, and its indexes, which are structures used to support data access.
Practice methods and selection criteria
Choose a learning method by whether you need syntax reference, a visible demonstration, or repeated query edits with feedback. For joins, the most useful test is whether you can predict the output before running the statement.
| Approach | Active query editing | Result verification | Feedback source | Best use |
|---|---|---|---|---|
| Hands-on practice with feedback | You edit and run queries | Compare actual rows with the required result | Exercise checks and hints | Correcting syntax and row-selection mistakes |
| Video courses | Pause and reproduce demonstrations in your editor | Compare your output with the demonstrated result | The explanation and your own checks | Watching how a query is built |
| Books | Type and modify worked examples | Compare with supplied outputs when available | Explanations and exercise answers when included | Building a structured understanding |
| Free documentation | Adapt documented syntax in your editor | Run a small test with known expected rows | Syntax rules and your database's messages | Resolving exact grammar questions |
The Work Gym provides hands-on practice in working tools in the browser. Exercises are checked automatically, with a hint that names the mistake. Use feedback to identify the incorrect choice, then explain why the corrected query returns the required rows.
Repeat the unmatched-row decision with LEFT JOIN: keep rows without a match. Before running your query, state which left-side records should remain.
Before moving to another join type, reproduce both valid employee outputs without changing the starting data or ON condition. You should be able to explain why Mira stays in both results, why Owen stays only with the left join, and why the invalid keyword combination produces no result set.
Common questions
Is LEFT INNER JOIN valid SQL syntax? LEFT INNER JOIN isn't valid PostgreSQL syntax. Use LEFT JOIN to preserve unmatched left-side rows or INNER JOIN to return only matching combinations.
Are LEFT JOIN and LEFT OUTER JOIN equivalent? Yes, LEFT JOIN and LEFT OUTER JOIN mean the same thing in PostgreSQL. OUTER is optional, so changing only that keyword doesn't change the result.
What does an INNER JOIN return in the employee example? INNER JOIN returns Mira with the department name Support. Owen doesn't appear because the departments input has no record with department_id 20.
How does RIGHT JOIN differ from LEFT JOIN? RIGHT JOIN preserves every row from the right input, while LEFT JOIN preserves every row from the left input. In PostgreSQL, you can express a right join as a left join by swapping the inputs and adjusting the column references as needed.