There are 2 transmission and car tables:

  id_transmission | name -----------------+---------- 1 | trans_01 2 | trans_02 3 | trans_03 id_car | name | id_transmission // references transmission (id_transmission) --------+-------+---------------- 1 | car_1 | 1 2 | car_2 | 2

I make a request to find unused parts:

 select m.name from motor as m left outer join car as c on m.id_motor = c.id_motor where c.name is null;

Everything is good, I get exactly the unused parts. But when I change where to and make this request:

 select t.name from transmission as t left outer join car as c on t.id_transmission = c.id_transmission and c.name is null;

That gets all the details at all.

How does it come about why in this context where and and not synonymous?

    1 answer 1

    The selection in the ON section is performed BEFORE the binding, and in the WHERE section - after the binding.

    The first request works as you intended, but the second one solves another task, namely “to get all the transmissions that are used at least in one car and for which the name field is not empty”.

    UPD: was inattentive. The second query solves the problem "to get all the transmissions, the number of duplicates is equal to the number of uses, or 1 for unused anywhere".

    • but in the example with and I have all the transmissions and those that are not used. You do not confuse? - Pavel
    • one
      And, you have the same table of transmissions (and in the texts of requests - motors) on the left ... well then, in general, everything will be displayed regardless of whether they are used where or not. Simply, if used, there will be a corresponding number of duplicates. - Akina
    • yes sorry corrected, I just have there several similar tables with details, they only differ in names. - Pavel