今天看啥
热点:

left join,right join,inner join,joininner


数据库中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

 

www.bkjia.comtruehttp://www.bkjia.com/Sql_Server/1283995.htmlTechArticleleft join,right join,inner join,joininner 数据库中 left join,right join,inner join 的差异 具体详细说明 总的来说: JOIN: 有匹配的就返回。 LEFT JOIN: 右表...

相关文章

相关搜索: join

帮客评论

视觉看点