Select * from t2 where id > 1; select * from T2 where id > 1

It’s kind of confusing. I don’t think it makes sense. So I tried it myself.

First, review how left Join is implemented

Mysql implements slave processing of left join in a nested loop, as shown in the following statement:

SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)
Copy the code

Pseudo code:

FOR each row lt in lt {BOOL b = FALSE; FOR each row rt in rt to that P1 (lt, rt) {/ / traverse right table each line, line find meet the join condition IF P2 (lt, rt) {/ / t where filtering condition: lt = | | rt. } b=TRUE; // lt has a corresponding line in RT} IF (! B) {/ / traverse the RT, found lt didn't have the corresponding row in the RT, then try to use null to fill a line IF P2 (lt, null) {/ / up null after meet the where filter conditions t: = lt | | null; // Output lt and null fill line}}}Copy the code

It can be seen from the above that the execution process is roughly as follows:

  1. LT traverses each row of the left table (LT) and RT traverses each row of the right table (RT), combining (LT, RT).
  2. If (lt,rt) satisfies on condition P1(lt,rt), go to step 3, if not, go to step 4.
  3. Determine whether the WHERE condition P2(lt,rt) is met. If so, output,
  4. Spell lt and Null to (lt, Null). Output if the WHERE condition P2(lt,NULL) is met.

From the above steps, we can draw the following conclusions:

1. If you want to restrict the right table, it must be inonIn condition

2. Use WHERE to filter the left table

Then, the following two tables are generated in mysql

CREATE TABLE 't1' (' id 'decimal(10,0) DEFAULT NULL,' v1 'varchar(100) DEFAULT NULL, 'v11' decimal(10,0) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; CREATE TABLE 't2' (' tid 'decimal(10,0) DEFAULT NULL,' v2 'varchar(100) DEFAULT NULL, 'v22' decimal(10,0) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;Copy the code

The two tables have the following data respectively:

T1:

T2:

Execute statement:

select * from t1 left join t2 on t1.id = t2.tid

Results:

select * from t1 left join t2 on t1.id = t2.tid and t1.id ! = ‘1’

select * from t1 left join t2 on t1.id = t2.tid and t2.tid ! = ‘1’

select * from t1 left join t2 on t1.id = t2.tid and t1.id > 1;

The SQL results for the next 3 are the same.

SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL If it doesn’t work it will be the same as the first SQL

In fact, the execution of the Left Join shows why this result occurs. All rows (lt,rt) in the left table with id = 1 do not satisfy P1(lt, RT). So lt will concatenate Null to (lt, Null) output.