【MySQL】连接查询

连接查询

内连接

#内连接：inner join
SELECT a.属性名1,a.属性名2,...,b,属性名1,b.属性名2... FROM table_name1 a 
inner join table_name2 b on a.id = b.id   #inner可以省略，默认join就是内连接
where a.属性名 满足某些条件;

#on a.uid = c.uid 区分大表和小表，按照数量来区分，小表永远是整表扫描，然后去大表扫描，所以给大表建索引是有用的
#从stuent小表中取出所有的a.uid，然后拿着这些uid去大表中搜索（大表中uid加索引提高效率）
select a.uid,a.name,a.age,a.sex,c.score from student a  #student a 即 student as a，as可以省略
inner join exam c on a.uid=c.uid
where c.uid=1 and c.uid=2;

自己和自己内连接来优化查询的应用场景：
如对于：select * from t_user limit 1500000,10，可以使用select * from t_user where id > 1500000 limit 10来提高效率，但是有时候不知道id具体的条件，无法使用where子句来查，则可以通过以下自己和自己做内连接来调高效率：（id是主键，id的limit 1500000比所有属性进行limit 1500000快，然后再从小表中取出所有的id，然后拿着这些id去大表中搜索，因为id是主键所有查询是常数级别）

#自己和自己的临时表内连接
select a.id,a.email,a.password from t_user a inner join(select id from from t_user limit 1500000,10) b on a.id=b.id;

左/右外连接

通过外连接可以判断哪些属性是NULL

# 左连接：left outer join，outer可以省略
# 先对左表进行整表扫描，把left这边的表所有的数据显示出来，在右表中不存在相应数据，则显示NULL
select a.* from User a left outer join Orderlist b on a.uid=b.uid where
a.orderid is null;

# 右连接：right outer join，outer可以省略
# 先对右表进行整表扫描，把right这边的表所有的数据显示出来，在左表中不存在相应数据，则显示NULL
select a.* from User a right outer join Orderlist b on a.uid=b.uid where
b.orderid is null;

左连接、右连接、内连接、全外连接区别

• left join：在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

• right join：在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

• inner join：在两张表进行连接查询时，只保留两张表中完全匹配的结果集。

• full join：在两张表进行连接查询时，返回左表和右表中所有没有匹配的行。

[left join,right join,inner join,full join之间的区别]

左连接示例：

注意：连接查询里的where和on

对于inner join内连接，过滤条件写在where的后面和on连接条件里面，效果是一样的，mysql会把on的条件翻译成where（因为where后面的过滤条件可以通过索引来进行过滤），但是对于外连接来说是不一样的：

select a.* from student a inner join exame b on a.uid=b.uid where b.cid=3;
select a.* from student a left join exame b on a.uid=b.uid where b.cid=3;
select a.* from student a left join exame b on a.uid=b.uid and b.cid=3;

上图的sql语句查询结果是一样的，因为left join后面的where条件会先执行，而并不是按左连接那样先去扫描左表，所以这样写就和内连接查询结果一样了

如下图这样写就可以得到正确的左连接查询结果：

具体sql执行信息可以通过expalin来查看：

select a.* from student a inner join exame b on a.uid=b.uid where b.cid=3;
select a.* from student a left join exame b on a.uid=b.uid where b.cid=3;

如下图：这两句语句的执行是相同的：都是先using where来过滤b表，然后b表就是一个小表，再拿小表去大表a里面搜索

当把过滤条件换成and之后：

select a.* from student a left join exame b on a.uid=b.uid and b.cid=3;

如下图：会先对左表a做整表扫描，符合左连接查询的流程

因此：

使用外连接查询的时候要留意：过滤条件使用on…and这样的而不是where，where的条件只用来作null值判断