前言

写SQL题经常用，遇到的在这里记录一下

1. join on 和 where 的条件区别

MySQL5.7 join 相关文档

1.1 用 left join 取代 right join

RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

right join 和 left join 工作原理类似，为了保证写出的sql语句具有可移植性，建议用left join 取代 right join 。

1.2 相同点

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

在没有连接条件或过滤条件的情况下，以下两个语句的语义相同，都生成笛卡尔积

select * from a join on b; # 当且仅当是inner join 而不是其他连接
select * from a, b;

1.3 不同点

The search_condition used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set.

与ON连用的条件也可以写在WHERE子句中。通常，ON子句用于指定如何联接表的条件，WHERE子句限制要在结果集中包括哪些行。

2. is null

MySQL5.7 IS NULL 相关文档

2.1 is null 可以走索引

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

2.2 避免使用 where 字段 = null

以NULL做where条件过滤时应该写 IS NULL;

WHERE ... IS NULL;