表的关系
- 一对一，一张表 有时候会为了性能而拆开(商品信息和商品备注信息–详情-很大）
- 一对多，必须两张表
- 多对一，反过来就是一对多，必须两张表
- 多对多，必须三张表，中间表，来维护它们之间的关系
笛卡尔积查询：
- 所谓笛卡尔积查询就是指，查询两张表，其中一张表有m条记录，另一张表有n条记录，查询的结果是m*n条。
笛卡尔积。
现在，我们有两个集合A和B。 A = {0,1} B = {2,3,4}
集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：
A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；
B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；
以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。
查询复杂度
例如: A表 n条记录,B表m条记录
- 表连接查询 select * from A left B on , 复杂度 n+m
- 相关子查询 select * from A IN (select * from B) , 复杂度 n*m
所以 mysql查询优化器可能对涉及子查询的查询语句进行重写, 转变为多表查询的操作,已降低查询的复杂度
案例:
tab1
tab2
SELECT * FROM tab1 ,tab2 ;
- 笛卡尔积=3*3=9
where 和 left join区别
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
区别：
1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2. where条件是在临时表生成好后，再对临时表进行过滤的条件。
这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
案例:
tab1
tab2
WHERE:
SELECT * FROM tab1 ,tab2 WHERE tab1.size = tab2.size ;
LEFT JOIN ON:
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.size = tab2.size;
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.size != tab2.size
解析:
1.数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表
相当于 SELECT * FROM tab1 ,tab2 ; ==> 中间的临时表
2.LEFT JOIN on条件是在生成临时表时使用的条件，
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.size != tab2.size;
其中tab1.size != tab2.size 是条件
3.不管LEFT JOIN on中的条件是否为真，都会返回左边表中的记录
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.size = tab2.size;
4. 我们再看where 多表联查:
SELECT * FROM tab1 , tab2 WHERE tab1.size = tab2.size;
where条件是在临时表生成好后，再对临时表进行过滤帅选
同理:
SELECT * FROM tab1 , tab2 WHERE tab1.size != tab2.size;
LEFT JOIN , RIGHT JOIN ,INNER JOIN对比
概念:
- left join(左联接) ：把左边的全部查出来，右边有的则匹配，没有则为null
- right join(右联接) ：返回包括右表中的所有记录,和左表中联结字段相等的记录
- inner join(等值连接)： 只返回两个表中联结字段相等的行
案例:
tab1
tab2
SELECT * FROM tab1 ,tab2;
条件为tab1 .size = tab2.size;
SELECT * FROM tab1 , tab2 WHERE tab1.size = tab2.size;
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.size = tab2.size;
SELECT * FROM tab1 RIGHT JOIN tab2 ON tab1.size = tab2.size;
SELECT * FROM tab1 INNER JOIN tab2 ON tab1.size = tab2.size;
条件为tab1 .size != tab2.size
SELECT * FROM tab1 , tab2 WHERE tab1.size != tab2.size;
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.size != tab2.size;
SELECT * FROM tab1 RIGHT JOIN tab2 ON tab1.size != tab2.size
SELECT * FROM tab1INNER JOIN tab2 ON tab1.size != tab2.size
另: 只查询左表字段的的记录
SELECT tab1.id, tab1.salary, tab1.size
FROM tab1 LEFT JOIN tab2 ON tab1.size != tab2.size;
看上去像有重复记录,实际是没查全
中间临时表
加上 DISTINCT
SELECT DISTINCT tab1.id, tab1.salary, tab1.size
FROM tab1 LEFT JOIN tab2 ON tab1.size != tab2.size
解析:
因为on的条件tab1.size != tab2.size ,左表一条匹配到右边记录多条,所以单看左表会显示重复记录
如果要唯一 可以使用where 在on之后进行筛选,或者on后的条件 是一 一匹配.
题目:
需求:找出表tab1中 size和tab2中size不相等的记录
tab1
tab2
不能直接用left join on 去找
SELECT tab1.id,tab1.salary,tab1.size
FROM tab1 LEFT JOIN tab2 ON tab1.size != tab2.size;
因为: tab1.size != tab2.size ,左表一条匹配到右边记录多条,所以单看左表会显示重复记录
正确写法 1:
先找到相等的记录 ,在筛选
SELECT tab1.id,tab1.salary,tab1.size FROM tab1 WHERE tab1.id NOT IN (
SELECT tab1.id FROM tab1 INNER JOIN tab2 ON tab1.size = tab2.size );
或者
SELECT tab1.id,tab1.salary,tab1.size FROM tab1 WHERE tab1.id NOT IN (
SELECT tab1.id FROM tab1 , tab2 WHERE tab1.size = tab2.size );
注意:
上诉sql不能用left JOIN on 或者 right JOIN on
left 会默认返回所有 tab1.id
SELECT tab1.id FROM tab1 LEFT JOIN tab2 ON tab1.size = tab2.size
right 返回结果集 会有null 记录
- SELECT tab1.id FROM tab1 RIGHT JOIN tab2 ON tab1.size = tab2.size
INNER JOIN
SELECT * FROM tab1 INNER JOIN tab2 ON tab1.size = tab2.size
总结：
-
对于left join，不管on后面跟什么条件，左表的数据全部查出来，因此要想过滤需把条件放到where后面
-
对于inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用。也可以把条件放到where后面。
正确写法 2:
not in 效率不高 可以考虑 not exists
SELECT id ,size,salary FROM tab1 AS a WHERE NOT EXISTS(
SELECT tab1.id FROM tab1 INNER JOIN tab2 =ON tab1.size =tab2.size
WHERE tab1.id= a.id )
正确写法 3:
SELECT tab1.id,tab1.salary,tab1.size FROM tab1 WHERE size NOT IN (
SELECT size FROM tab2 )
