Resolve the difference between on and where in left_join and inner_join in sql statements

  • 2020-05-27 07:19:56
  • OfStack

table a(id, type):
id type
----------------------------------
1 1
2 1
3 2
table b(id, class):
id class
---------------------------------
1 1
2 2
sql statement 1: select a.*, b.* from a left join b on a.id = b id and a type = 1;
sql statement 2: select a.*, b.* from a join a id = b id a type = 1;
sql statement 3: select a.*, b.* from a left b b a.id = b.id and b class = 1;

The execution result of sql statement 1 is:
a.id a.type b.id b.class
----------------------------------------
1 1 1 1
2 1 2 2
3 2

The result of sql statement 2 is:
a.id a.type b.id b.class
----------------------------------------
1 1 1 1
2 1 2 2

The execution result of sql statement 3 is:
a.id a.type b.id b.class
----------------------------------------
1 1 1 1
2 1
3 2
As can be seen from sql statement 1, all the records in the left table of left join will be queried and displayed. The conditions after on will not affect on unless it is filtered by where. This is sql statement 2. As you can see from sql statement 3, in the conditions following on, the restriction from the right table will come into play.
**************************************************************************
sql statement 4: select a.*, b.* from a join on on id = b and a type = 1;
sql statement 5: select a.*, b.* from a on a id = b where a type = 1;
sql statement 6: select a.*, b.* from a, b where a.id = b.id and a.
sql statement 7: select a.*, b.* from a, b where a.type = 1 and a.id = b id;

The execution results of these four statements are as follows:
a.id a.type b.id b.class
----------------------------------------
1 1 1 1
2 1 2 2
It can be seen that the restrictions on on in inner join will all be in effect, which is the same as the result of where. In addition, the where statement does yield the same results as the inner join statement, but with different efficiencies (I haven't tested this, but I believe it).
I don't have enough data to test whether sql statement 6 is any less efficient than sql statement 7, but I believe it is.

Related articles: