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
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?
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