MySQL多表联合查询语句的编写及效率分析、优化

日期：2020-06-02 栏目：程序人生浏览：次

一、多表连接类型

1. 笛卡尔积(交叉连接)
在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN，或者使用',' 如：

SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,table2 SELECT * FROM users CROSS JOIN articles; SELECT * FROM users JOIN articles; SELECT * FROM users, articles;

由于其返回的结果为被连接的两个数据表的乘积，因此当有WHERE, ON或USING条件的时候一般不建议使用，因为当数据表项目太多的时候，会非常慢。一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN

2. 内连接INNER JOIN
在MySQL中把INNER JOIN叫做等值连接，即需要指定等值连接条件在MySQL中CROSS和INNER JOIN被划分在一起。

SELECT * FROM users as u INNER JOIN articles as a where u.id = a.user_id

3. MySQL中的外连接
分为左外连接和右连接，即除了返回符合连接条件的结果之外，还要返回左表(左连接)或者右表(右连接)中不符合连接条件的结果，相对应的使用NULL对应。

例子：
users表：

+----+----------+----------+--------------------+ | id | username | password | email | +----+----------+----------+--------------------+ | 1 | junxi | 123 | xinlei3166@126.com | | 2 | tangtang | 456 | xinlei3166@126.com | | 3 | ceshi3 | 456 | ceshi3@11.com | | 4 | ceshi4 | 456 | ceshi4@qq.com | | 5 | ceshi3 | 456 | ceshi3@11.com | | 6 | ceshi4 | 456 | ceshi4@qq.com | | 7 | ceshi3 | 456 | ceshi3@11.com | | 8 | ceshi4 | 456 | ceshi4@qq.com | | 9 | ceshi3 | 333 | ceshi3@11.com | | 10 | ceshi4 | 444 | ceshi4@qq.com | | 11 | ceshi3 | 333 | ceshi3@11.com | | 12 | ceshi4 | 444 | ceshi4@qq.com | +----+----------+----------+--------------------+

userinfos表：

+----+-------+--------+-------------+----------------+---------+ | id | name | qq | phone | link | user_id | +----+-------+--------+-------------+----------------+---------+ | 1 | 君惜 | 666666 | 16616555188 | | 1 | | 2 | 糖糖 | 777777 | 17717555177 | | 2 | | 3 | 测试3 | 333333 | 13313333177 | | 3 | +----+-------+--------+-------------+----------------+---------+

SQL语句：

SELECT * FROM users as u LEFT JOIN userinfos as i on u.id = i.user_id;

执行结果：

分析：
而users表中的id大于3的用户在userinfos中没有相应的纪录，但是却出现在了结果集中
因为现在是left join，所有的工作以left为准.
结果1，2，3都是既在左表又在右表的纪录4, 5, 6, 7, 8, 9, 10, 11, 12是只在左表，不在右表的纪录

工作原理：
从左表读出一条，选出所有与on匹配的右表纪录(n条)进行连接，形成n条纪录(包括重复的行)，如果右边没有与on条件匹配的表，那连接的字段都是null.然后继续读下一条。
引申：
我们可以用右表没有on匹配则显示null的规律, 来找出所有在左表，不在右表的纪录，注意用来判断的那列必须声明为not null的。
如：
SQL:
(注意:
1.列值为null应该用is null 而不能用=NULL
2.这里i.user_id 列必须声明为 NOT NULL 的.
）

SELECT * FROM users as u LEFT JOIN userinfos as i on u.id = i.user_id WHERE i.user_id is NULL;

执行结果：

转载注明出处：https://www.heiqu.com/5e49f4fa2758659cb0a8f067824e2a0f.html

MySQL多表联合查询语句的编写及效率分析、优化

相关推荐