sqlsql-server

Understanding AND in SQL LEFT Join


I have a question - while I am performing AND operation in LEFT JOIN SQL statement.

select * 
from customers 
left join salesman on customers.id = salesman.id 
                   and customers.id = 2

enter image description here

In the results I can see I am getting customers with id = 2 with other ids (1, 2, 3). I know AND will return output if both the left and right side of operation are true.

Why does the result set show all other values whose customer id is not equal to 2? How is the left join working internally?


Solution

  • Docs

    The LEFT join returns all rows in the "left" table. Adding the constraint on customers id isn't going to change that fact.

    If you want to filter those rows, you need to use a where clause:

    SELECT * FROM customers
    LEFT OUTER JOIN salesman
    ON customers.id = salesman.id
    WHERE customers.id = 2