Country or region and language

    Choose your country or region and your language.

    Suggested for you

      All countries and regions

        Choose your character

        It shows in the top bar and on your profile. Change it any time.

        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.

        A person practicing on a laptop in a quiet workspace

        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.

        Ways to learn and practice SQL joins
        ApproachActive query editingResult verificationFeedback sourceBest use
        Hands-on practice with feedbackYou edit and run queriesCompare actual rows with the required resultExercise checks and hintsCorrecting syntax and row-selection mistakes
        Video coursesPause and reproduce demonstrations in your editorCompare your output with the demonstrated resultThe explanation and your own checksWatching how a query is built
        BooksType and modify worked examplesCompare with supplied outputs when availableExplanations and exercise answers when includedBuilding a structured understanding
        Free documentationAdapt documented syntax in your editorRun a small test with known expected rowsSyntax rules and your database's messagesResolving 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.

        Practice what this post teaches

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        Help and feedback

        Ask a question, tell us what is not working, or share an idea. We reply by email.

        What is it about?

        We use it only to reply to you.