T.m aren't gr is the six Numbers, why is not the same as the query results?

CodePudding user response:

Select distinct e.e name from emp e join (select distinct MGR from emp where MGR is not null) t on e.e mpno not in (t.m gr);
It is your first SQL, the result is 14 records, namely all records, no filter,
The main problem is the part of the scarlet letter "join", should be rewritten as "inner join",
Your statement practical effect is on the left side of the emp "e", there is no connection to the record will show that is equivalent to the left of the join effect,

CodePudding user response:

The

reference 1st floor master replies:

select distinct e.e name from emp e join (select distinct MGR from emp where MGR is not null) t on e.e mpno not in (t.m gr);
It is your first SQL, the result is 14 records, namely all records, no filter,
The main problem is the part of the scarlet letter "join", should be rewritten as "inner join",
Your statement practical effect is on the left side of the emp "e", there is no connection to the record will show that the effect of the equivalent of a left join,

In MySQL, JOIN, CROSS the JOIN, and INNER joins are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. The INNER JOIN is 2 with an ON clause, CROSS JOIN is 2 otherwise.

CodePudding user response:

on is an expression of the heel, Boolean,

on e.e mpno value is true or not, depends on your two kind of statement execution plan, use the explain see,

Your SQL statement is a bug, please change to the on e.e mpno=t.m gr ,