【MySQL】关键字及运算符官方文档及笔记
2022/2/4 2:14:10
本文主要是介绍【MySQL】关键字及运算符官方文档及笔记,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
前言
写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;
这篇关于【MySQL】关键字及运算符官方文档及笔记的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-25MySQL报错Duplicate entry '0' for key 'PRIMARY'
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享