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.

        SQL JOIN and LEFT JOIN with checked results

        Learn SQL JOIN and LEFT JOIN with a runnable PostgreSQL example. Check missing matches, repeated rows, and filters to choose the right join for your report.

        A person practicing on a laptop in a quiet workspace

        In PostgreSQL, SQL JOIN and LEFT JOIN differ in whether they keep rows without a match: JOIN returns matching row pairs only, while LEFT JOIN also keeps unmatched rows from the left table. For an unmatched row, LEFT JOIN supplies NULL in the right-side columns. Use JOIN for customers with orders and LEFT JOIN when customers without orders must remain visible.

        The difference in returned rows

        JOIN means INNER JOIN in PostgreSQL, and LEFT OUTER JOIN means the same thing as LEFT JOIN. The important choice isn't the longer spelling; it's whether your report must preserve unmatched rows.

        A join connects rows using a matching condition, such as the same customer identifier in two tables. Each matching pair produces an output row. If one customer has two orders, both join types return two rows for that customer.

        Before running a query, write a match ledger: a small record of how many matches each customer has and how many output rows you expect. The ledger helps you distinguish a missing customer from a legitimate repeated customer name.

        For each row in the left table:

        • JOIN produces one output row for every matching right-side row, or no output row when there isn't a match.
        • LEFT JOIN produces one output row for every matching right-side row, or one preserved row when there isn't a match.
        • Neither join type promises one output row per customer.

        Three customers and a match ledger

        The example uses three fictional customers with two, one, and zero orders, so you can see both repeated matches and an unmatched customer. Every query below uses PostgreSQL syntax.

        customers
        customer_id | customer_name
        1           | Ava
        2           | Bo
        3           | Cy
        
        orders
        order_id | customer_id | status
        101      | 1           | open
        102      | 1           | closed
        103      | 2           | open

        The matching key is customer_id, the identifier that connects an order to a customer. Don't match on customer names: different customers can share a name, and a spelling change shouldn't break the connection.

        Count the orders for each identifier before writing the join. Ava has two matches, Bo has one, and Cy has none. Your ledger should predict the following results.

        customer | matching orders | JOIN rows | LEFT JOIN rows
        Ava      | 2               | 2         | 2
        Bo       | 1               | 1         | 1
        Cy       | 0               | 0         | 1
        Total    | 3               | 3         | 4

        The queries use common table expressions, which are named query results introduced by WITH and available within the statement. VALUES supplies the fictional records directly. You can copy each query into a PostgreSQL query editor and run it independently without creating permanent tables.

        Practice what this post teaches

        Two joins against the same records

        Changing JOIN to LEFT JOIN adds Cy's unmatched row without removing either of Ava's matching orders. Run the matching-only query first and compare its output with the ledger.

        Matching customers and orders

        WITH customers(customer_id, customer_name) AS (
          VALUES (1, 'Ava'), (2, 'Bo'), (3, 'Cy')
        ), orders(order_id, customer_id, status) AS (
          VALUES (101, 1, 'open'), (102, 1, 'closed'),
                 (103, 2, 'open')
        )
        SELECT c.customer_name, o.order_id
        FROM customers AS c
        JOIN orders AS o
          ON c.customer_id = o.customer_id
        ORDER BY c.customer_id, o.order_id;
        
        -- Result:
        -- customer_name | order_id
        -- Ava           | 101
        -- Ava           | 102
        -- Bo            | 103

        The aliases c and o are short names for customers and orders. ON supplies the matching condition. ORDER BY makes the displayed order predictable; without ORDER BY, don't rely on the database returning rows in a particular sequence.

        Cy doesn't appear because no order has Cy's customer identifier. Ava appears twice because two different orders match Ava. Both observations agree with the ledger.

        Try predicting matching rows yourself with INNER JOIN: combine matching rows before running another query.

        Every customer, including unmatched customers

        WITH customers(customer_id, customer_name) AS (
          VALUES (1, 'Ava'), (2, 'Bo'), (3, 'Cy')
        ), orders(order_id, customer_id, status) AS (
          VALUES (101, 1, 'open'), (102, 1, 'closed'),
                 (103, 2, 'open')
        )
        SELECT c.customer_name, o.order_id
        FROM customers AS c
        LEFT JOIN orders AS o
          ON c.customer_id = o.customer_id
        ORDER BY c.customer_id, o.order_id;
        
        -- Result, with NULL written explicitly:
        -- customer_name | order_id
        -- Ava           | 101
        -- Ava           | 102
        -- Bo            | 103
        -- Cy            | NULL

        Customers is the left table because it appears before LEFT JOIN. Cy now contributes one preserved row. NULL means there's no matching order value in that row, not that Cy has an order numbered zero. Your query editor might display NULL as an empty cell or another marker.

        Practice preserving unmatched rows with LEFT JOIN: keep rows without a match. Write your expected row count before you run the query.

        Open orders without lost customers

        To keep every customer while showing only open orders, put the order-status condition in ON alongside the matching key. The condition changes which orders qualify as matches, not which customers must remain.

        WITH customers(customer_id, customer_name) AS (
          VALUES (1, 'Ava'), (2, 'Bo'), (3, 'Cy')
        ), orders(order_id, customer_id, status) AS (
          VALUES (101, 1, 'open'), (102, 1, 'closed'),
                 (103, 2, 'open')
        )
        SELECT c.customer_name, o.order_id
        FROM customers AS c
        LEFT JOIN orders AS o
          ON c.customer_id = o.customer_id
         AND o.status = 'open'
        ORDER BY c.customer_id, o.order_id;
        
        -- Result, with NULL written explicitly:
        -- customer_name | order_id
        -- Ava           | 101
        -- Bo            | 103
        -- Cy            | NULL

        Ava's closed order no longer qualifies. Ava and Bo each have one open order, while Cy still has no qualifying order. The filtered ledger therefore predicts one output row per customer for these particular records.

        The common mistake is moving the open-status condition from ON to WHERE. WHERE filters rows after the join has formed its result. Cy's preserved row has NULL for the order status, and comparing NULL with the text 'open' doesn't evaluate to true. WHERE keeps only rows whose condition evaluates to true, so Cy disappears.

        For these records, putting the open-status condition in WHERE would return only Ava with order 101 and Bo with order 103. If the task says every customer must remain, that two-row result contradicts the ledger. Move the right-side status restriction back into ON.

        Checks before using the result

        Check both the intended meaning of each output row and the expected contribution from each customer before using a joined result. The original queries return customer-order pairs, with an extra unmatched-customer row in the LEFT JOIN result.

        Use these checks when a result looks wrong:

        • Missing customer: confirm whether the task requires JOIN or LEFT JOIN, then inspect right-side conditions in WHERE.
        • Repeated customer: compare order identifiers and count the customer's matching orders.
        • Unexpected NULL: check whether any right-side row satisfies every condition in ON.
        • Unexpected total: compare each customer's actual row count with the ledger instead of checking only the overall count.

        Don't add DISTINCT just because Ava appears twice. DISTINCT removes duplicate selected rows; it doesn't decide which order belongs in a customer summary. With the order identifier selected, Ava's rows are already different. A one-row-per-customer summary needs a separate rule, often aggregation, which combines rows into summaries.

        For a final exercise, change Bo's order status from open to closed in the filtered example. Predict the result before running the query: the total should remain three rows, but Bo's order identifier should become NULL.

        Use the same predict-then-check method with Practice: combine tables on The Work Gym. Compare the checked result with your ledger to locate the mistake.

        WITH customers(customer_id, customer_name) AS (
          VALUES (1, 'Ava'), (2, 'Bo'), (3, 'Cy')
        ), orders(order_id, customer_id, status) AS (
          VALUES (101, 1, 'open'), (102, 1, 'closed'),
                 (103, 2, 'closed')
        )
        SELECT c.customer_name, o.order_id
        FROM customers AS c
        LEFT JOIN orders AS o
          ON c.customer_id = o.customer_id
         AND o.status = 'open'
        ORDER BY c.customer_id, o.order_id;
        
        -- Result, with NULL written explicitly:
        -- customer_name | order_id
        -- Ava           | 101
        -- Bo            | NULL
        -- Cy            | NULL

        Bo has an order in the starting data, but no order that qualifies under the complete ON condition. That's why Bo now receives a preserved row rather than order 103.

        The ledger method checks row preservation and multiplication. It doesn't cover aggregation or query performance, and counting matches manually becomes slow on large tables. Use a small, inspectable set of records to verify the join's logic before applying that logic to a larger report.

        Common questions

        Is JOIN the same as INNER JOIN in PostgreSQL? Yes. In PostgreSQL, JOIN without another join type means INNER JOIN, which returns matching row pairs only. Writing INNER JOIN explicitly can make your intention clearer to another reader.

        Is LEFT JOIN different from LEFT OUTER JOIN? No. In PostgreSQL, LEFT JOIN and LEFT OUTER JOIN mean the same thing. Both preserve unmatched left-side rows and supply NULL for their right-side columns.

        Why does a LEFT JOIN return more rows than the left table? A left-side row produces one output row for each matching right-side row. If one customer has several matching orders, that customer appears several times. Preserving customers doesn't mean returning each customer only once.

        Why does a WHERE condition remove unmatched LEFT JOIN rows? WHERE keeps only rows whose condition evaluates to true. An unmatched row has NULL in the right-side columns, so an equality comparison against a right-side status doesn't evaluate to true. Put the right-side restriction in ON when you need to preserve every left-side row.

        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.