NULL rejection in mysql

Question

Today i was reading MySQL Docs on outer joins ,there I found that certain conditions are null rejected when an outer join is done

T1 LEFT JOIN T2 ON T1.A=T2.A

Conditions such as these are null-rejected:(not able to understand from docs original docs)

T2.B IS NOT NULL,
T2.B > 3,
T2.C <= T1.C,
T2.B < 2 OR T2.C > 1

Can anyone explain in simple words .

1. Why are these conditions rejected
2. What is the affect on output produced by the query if some conditions are rejected.

Answer 1

This discussion of "null-rejected" conditions is simply about optimizing queries that are more complex than they need to be; it doesn't change the behavior of a query at all.

Consider this query:

select * from table1
    left outer join table2 on table1.id=table2.id
    where table2.id is not null

This query is written as an outer join, and yet it also discards any row in which table2 is null (the null-rejected condition is the condition in the where clause that excludes any rows that are null).

The where clause in this query rejects any of the "outer rows" from the outer join. This means that it is not really an outer join at all; it could be rewritten as this:

select * from table1
    inner join table2 on table1.id=table2.id

MySQL recognizes this situation and automatically optimizes to the simplified query, rather than doing all the extra work of performing an outer join and discarding the rows.