0

I have two tables

A id, b_id

and

B id, arbitrary_binary_value

arbitrary_binary_value is really a field that is 1 or 0, each otherwise unique row is duplicated with a 1 and a 0, this is just some db I am working with

Anyway, I wrote a query

select * from a left join b on a.b_id = b.id
where b.arbitrary_binary_value = 0

and I thought it would yield the same number as

select * from a

but it did not,

so I changed query to

select * from a left join b on a.b_id = b.id
AND b.arbitrary_binary_value = 0

and it did yield the same number as the number of rows in a, as expected

Why did the where clause not behave as expected, when the and clause did?

amchugh89
  • 1,276
  • 1
  • 14
  • 33
  • Not helpful, I found the answer here though https://stackoverflow.com/a/20981676/2089889 – amchugh89 Jun 28 '23 at 21:54
  • The first one is also functionally equivalent to `SELECT * FROM a INNER JOIN b ON a.b_id = b.id WHERE b.arbitrary_binary_value = 0` since placing a condition on the left table in the where clause causes an implicit INNER JOIN. – JNevill Jun 28 '23 at 22:53

0 Answers0