left join,right join,inner join

数据库中left join,right join,inner join的差异

 

具体详细说明

总的来说：

JOIN:                                                                    有匹配的就返回。 LEFT JOIN:                                                          右表中没有，没关系，我左表的全部返回。 RIGHT JOIN:                                                        左表中没有就空着，右表的全部返回。 FULL JOIN:                                                          俩表中有一个就返回。

常用的来说，inner join，left join，其中inner join 经常省略了。

 

1、内连接(等值连接)

将两个表中存在连结关系的字段符合连接条件的记录形成记录集。只返回两个表中联结字段相等的行

Select A.name,B.name from A inner join B on A.id=B.id和

Select A.name,B.name from A,B where A.id=B.id结果是一样的（内连接的inner关键字可省略）；

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

INNER JOIN 关键字语法

SELECT column_name(s)

FROM table_name1

INNER JOIN table_name2

ON table_name1.column_name=table_name2.column_name

 

2、外连接：分为左外连接和右外连接

left join左连接A、B表结果包括A的全部记录和符合条件的B的记录。返回包括左表中的所有记录和右表中联结字段相等的记录。

Select A.name,B.name from A Left Join B on A.id=B.id

其中LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

LEFT JOIN 关键字语法

SELECT column_name(s)

FROM table_name1

LEFT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

连接通常可以在select语句的from子句或where子句中建立，其语法格式为：

select colunm_name1,colunm_name2

from table_name1

left join table_name2

on table_name1.colunmname=table_name2.colunmname

其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接称为自连接， join_type 为连接类型，可以是left join 或者right join 或者inner join 。

 

left join与where共同使用

select a.*,b.*

from table1 a

left join table2 b on b.X=a.X

where XXX

如上：一旦使用了left join，没有where条件时，左表table1会显示全部内容。使用了where，只有满足where条件的记录才会显示（左表显示部分或者全部不显示）

so。。。。

left join的困惑：一旦加上where条件，则显示的结果等于inner join 

 

原因分析：

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户；

where条件是在临时表生成好后，再对临时表进行过滤的条件；

因此：where 条件加上，已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。 

 

解决方案：

1、where过滤结果作为子查询，和主表left，如下：

select a.*,tmp.*from table1 aleft join(

    select a.*,b.*

    from table1 a

    left join table2 b on b.X=a.X

    where XXX

)tmp

很明显，子查询语句无论 left join、inner join都没啥区别了

2、查询条件放在on后面

select a.*,b.*

from table1 a

left join table2  b

       on b.X=a.X and XXX

注意：where XXX去掉，改为链接条件on后面的 and XXX

分析：on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

结论：过滤条件放在：

where后面：是先连接然生成临时查询结果，然后再筛选。

on后面：先根据条件过滤筛选，再连 生成临时查询结果。

对于left join，不管on后面跟什么条件，左表的数据全部查出来，因此要想过滤需把条件放到where后面

对于inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用。也可以把条件放到where后面。

 

on、where、having的区别

   on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。

  但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。       

   在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的。  
   如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。     
   在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。

  由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里  

 

 

 

标准的模板：

select * from (

select 表A.*,表B.*

from 表A  left join 表B on 表A.x=表B.x )

where 表A.y<>表B.y or 表B.x is null;

 

left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的。

换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID)。B表记录不足的地方均为NULL。

 

right join右连接A、B表的结果和左连接B、A的结果是一样的，返回包括右表中的所有记录和左表中联结字段相等的记录。也就是说：

Select A.name,B.name from B Right Join A on B.id-A.id执行后的结果是一样的。

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

RIGHT JOIN 关键字语法

SELECT column_name(s)

FROM table_name1

RIGHT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

 

一个误区：

想查询B表中的所有type为1的数据，A表中查出一些字段加入到B结果中。

比较下下面两句SQL

1.select * from A right join B on B.type=1 where A.id=B.aid;

2.select * from B left join A on A.id=B.aid where B.type=1 ;

我原先是按第一句写的，结果出来时，我感到很奇怪。为什么right join，不能把右边表里type为1的数据全部获取出来。

 

第一句，先执行on部分，查出了所有B的数据，然后和A进行右连接，最后根据条件A.id=B.aid，筛选数据，这样如果不满足A.id=B.aid的数据，将会从结果中去除，包括B的数据！

第二句，先执行on部分，查出A中所有满足A.id=B.aid的数据，再进行左连接，最后根据type=1做筛选。

可见，第二句sql才是正确的方法。

 

 

3，全连接Full join

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。

FULL JOIN 关键字语法

SELECT column_name(s)

FROM table_name1

FULL JOIN table_name2

ON table_name1.column_name=table_name2.column_name

 

下面列出了这几个可以使用的 JOIN 类型，以及它们之间的差异。

JOIN: 如果表中有至少一个匹配，则返回行 LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行 RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行 FULL JOIN: 只要其中一个表中存在匹配，就返回行

 

 

 

 

 多余：

三表联结查询

select username,psw,gname,tel from (t1 left join t2 on t1.t1_id=t2.t1_id) left join t3 on t1.t1_id=t3.t1_id

 

三个表信息：

tems：商品表，item_visit_stats：商品访问表，item_trade_stats：商品销售表

 

SELECT i.num_iid, i.title, i.price, SUM(iv.user_visits) AS uv,it.buyer_num,it.item_num,it.item_num*i.price AS turnover
FROM (

items AS i RIGHT JOIN item_visit_stats AS iv ON i.num_iid=iv.num_iid)
LEFT JOIN (

SELECT num_iid,SUM(buyer_num) AS buyer_num,SUM(item_num) AS item_num FROM item_trade_stats
WHERE seller_nick="XXXX" AND business_day BETWEEN '2017-08-14' AND '2017-08-15' GROUP BY num_iid)
AS it ON it.num_iid=iv.num_iid 
WHERE i.nick="XXXX" AND iv.business_day BETWEEN '2017-08-14' AND '2017-08-15'
GROUP BY i.num_iid ORDER BY uv DESC