GVKun编程网logo

SQL JOIN和不同类型的JOIN

16

对于SQLJOIN和不同类型的JOIN感兴趣的读者，本文将提供您所需要的所有信息，并且为您提供关于(转载)Mysql----Join用法(Innerjoin,Leftjoin,Rightjoin,Cr

对于SQL JOIN和不同类型的JOIN感兴趣的读者，本文将提供您所需要的所有信息，并且为您提供关于(转载) Mysql----Join用法(Inner join,Left join,Right join, Cross join, Union模拟Full join)及---性能优化、Hive的join操作，left join,right join,inner join、INNER JOIN，LEFT JOIN，RIGHT JOIN和FULL JOIN有什么区别？ [重复]、join连接的五种方式的简单使用案例（Inner join,Left join,Right join,Full join,Cross join）的宝贵知识。

本文目录一览：

SQL JOIN和不同类型的JOIN

SQL JOIN和不同类型的JOIN

什么是SQL JOIN？有哪些不同的类型？

(转载) Mysql----Join用法(Inner join,Left join,Right join, Cross join, Union模拟Full join)及---性能优化

(转载) Mysql----Join用法(Inner join,Left join,Right join, Cross join, Union模拟Full join)及---性能优化

http://blog.csdn.net/ochangwen/article/details/52346610

 

前期数据准备

CREATE TABLE  atable(
aID int( 1 ) AUTO_INCREMENT PRIMARY KEY ,
aNum char( 20 ));

CREATE TABLE btable(
bID int( 1 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
bName char( 20 ) );

INSERT INTO atable
VALUES ( 1, ''a20050111'' ) , ( 2, ''a20050112'' ) , ( 3, ''a20050113'' ) , ( 4, ''a20050114'' ) , ( 5, ''a20050115'' ) ;

INSERT INTO btable
VALUES ( 1, '' 2006032401'' ) , ( 2, ''2006032402'' ) , ( 3, ''2006032403'' ) , ( 4, ''2006032404'' ) , ( 8, ''2006032408'' ) ;

-------------------------------------------------------------------------------------------

atable:左表；btable:右表。
JOIN 按照功能大致分为如下三类：
  1).inner join(内连接,或等值连接)：取得两个表中存在连接匹配关系的记录。
  2).left  join(左连接)：取得左表（atable）完全记录，即是右表（btable）并无对应匹配记录。
  3).right join(右连接)：与 LEFT JOIN 相反，取得右表（btable）完全记录，即是左表（atable）并无匹配对应记录。
注意：mysql不支持Full join,不过可以通过 union 关键字来合并 left join 与 right join来模拟full join.

一、Inner join

  内连接，也叫等值连接，inner join产生同时符合A和B的一组数据。
  接下来给出一个列子用于解释下面几种分类。如下两个表(A,B)

 
  1. mysql> select * from atable  inner join btable  on atable.aid=btable.bid;  
  2. +-----+-----------+-----+-------------+  
  3. | aID | aNum      | bID | bName       |  
  4. +-----+-----------+-----+-------------+  
  5. |   1 | a20050111 |   1 |  2006032401 |  
  6. |   2 | a20050112 |   2 | 2006032402  |  
  7. |   3 | a20050113 |   3 | 2006032403  |  
  8. |   4 | a20050114 |   4 | 2006032404  |  
  9. +-----+-----------+-----+-------------+  

 

二、Left join

  left join,（或left outer join:在Mysql中两者等价，推荐使用left join.）左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。

 

 
  1. mysql> select * from atable  left join btable  on atable.aid=btable.bid;  
  2. +-----+-----------+------+-------------+  
  3. | aID | aNum      | bID  | bName       |  
  4. +-----+-----------+------+-------------+  
  5. |   1 | a20050111 |    1 |  2006032401 |  
  6. |   2 | a20050112 |    2 | 2006032402  |  
  7. |   3 | a20050113 |    3 | 2006032403  |  
  8. |   4 | a20050114 |    4 | 2006032404  |  
  9. |   5 | a20050115 | NULL | NULL        |  
  10. +-----+-----------+------+-------------+  


------------------------------------------------------------------------------------------------------------

 

  2).如果想只从左表(A)中产生一套记录，但不包含右表(B)的记录，可以通过设置where语句来执行，如下

 

 
  1. mysql> select * from atable  left join btable  on atable.aid=btable.bid   
  2.     -> where atable.aid is  null or btable.bid is  null;  
  3. +-----+-----------+------+-------+  
  4. | aID | aNum      | bID  | bName |  
  5. +-----+-----------+------+-------+  
  6. |   5 | a20050115 | NULL | NULL  |  
  7. +-----+-----------+------+-------+  


-----------------------------------------------------------------------------------------

 

同理，还可以模拟inner join. 如下：


 

 
  1. mysql> select * from atable  left join btable  on atable.aid=btable.bid  where atable.aid is not null and btable.bid is not null;  
  2. +-----+-----------+------+-------------+  
  3. | aID | aNum      | bID  | bName       |  
  4. +-----+-----------+------+-------------+  
  5. |   1 | a20050111 |    1 |  2006032401 |  
  6. |   2 | a20050112 |    2 | 2006032402  |  
  7. |   3 | a20050113 |    3 | 2006032403  |  
  8. |   4 | a20050114 |    4 | 2006032404  |  
  9. +-----+-----------+------+-------------+  

------------------------------------------------------------------------------------------

 

三、Right join

  同Left join

 

  1. mysql> select * from atable  right join btable  on atable.aid=btable.bid;  
  2. +------+-----------+-----+-------------+  
  3. | aID  | aNum      | bID | bName       |  
  4. +------+-----------+-----+-------------+  
  5. |    1 | a20050111 |   1 |  2006032401 |  
  6. |    2 | a20050112 |   2 | 2006032402  |  
  7. |    3 | a20050113 |   3 | 2006032403  |  
  8. |    4 | a20050114 |   4 | 2006032404  |  
  9. | NULL | NULL      |   8 | 2006032408  |  
  10. +------+-----------+-----+-------------+  

 

四、差集

 

 
  1. mysql> select * from atable  left join btable  on atable.aid=btable.bid    
  2.     -> where btable.bid is null  
  3.     -> union  
  4.     -> select * from atable right join btable on atable.aid=btable.bid  
  5.     -> where atable.aid is null;  
  6. +------+-----------+------+------------+  
  7. | aID  | aNum      | bID  | bName      |  
  8. +------+-----------+------+------------+  
  9. |    5 | a20050115 | NULL | NULL       |  
  10. | NULL | NULL      |    8 | 2006032408 |  
  11. +------+-----------+------+------------+  


-----------------------------------------------------------------------------------

 

五.Cross join

  交叉连接，得到的结果是两个表的乘积，即笛卡尔积

    笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。

 

 
  1. mysql> select * from atable cross join btable;  
  2. +-----+-----------+-----+-------------+  
  3. | aID | aNum      | bID | bName       |  
  4. +-----+-----------+-----+-------------+  
  5. |   1 | a20050111 |   1 |  2006032401 |  
  6. |   2 | a20050112 |   1 |  2006032401 |  
  7. |   3 | a20050113 |   1 |  2006032401 |  
  8. |   4 | a20050114 |   1 |  2006032401 |  
  9. |   5 | a20050115 |   1 |  2006032401 |  
  10. |   1 | a20050111 |   2 | 2006032402  |  
  11. |   2 | a20050112 |   2 | 2006032402  |  
  12. |   3 | a20050113 |   2 | 2006032402  |  
  13. |   4 | a20050114 |   2 | 2006032402  |  
  14. |   5 | a20050115 |   2 | 2006032402  |  
  15. |   1 | a20050111 |   3 | 2006032403  |  
  16. |   2 | a20050112 |   3 | 2006032403  |  
  17. |   3 | a20050113 |   3 | 2006032403  |  
  18. |   4 | a20050114 |   3 | 2006032403  |  
  19. |   5 | a20050115 |   3 | 2006032403  |  
  20. |   1 | a20050111 |   4 | 2006032404  |  
  21. |   2 | a20050112 |   4 | 2006032404  |  
  22. |   3 | a20050113 |   4 | 2006032404  |  
  23. |   4 | a20050114 |   4 | 2006032404  |  
  24. |   5 | a20050115 |   4 | 2006032404  |  
  25. |   1 | a20050111 |   8 | 2006032408  |  
  26. |   2 | a20050112 |   8 | 2006032408  |  
  27. |   3 | a20050113 |   8 | 2006032408  |  
  28. |   4 | a20050114 |   8 | 2006032408  |  
  29. |   5 | a20050115 |   8 | 2006032408  |  
  30. +-----+-----------+-----+-------------+  
  31. 25 rows in set (0.00 sec)  
  32.   
  33.  <pre><code class="hljs cs"><span class="hljs-function">#再执行：mysql> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> A inner <span class="hljs-keyword">join</span> B</span>; 试一试 (与上面的结果一样)  
  34.   
  35. <span class="hljs-meta">#在执行mysql> select * from A cross join B on A.name = B.name; 试一试</span></code>  

 

    实际上，在 MySQL 中（仅限于 MySQL） CROSS JOIN 与 INNER JOIN 的表现是一样的，在不指定 ON 条件得到的结果都是笛卡尔积，反之取得两个表完全匹配的结果。    inner join 与 cross join 可以省略 inner 或 cross关键字，因此下面的 SQL 效果是一样的：

 
  1. ... FROM table1 INNER JOIN table2  
  2. ... FROM table1 CROSS JOIN table2  
  3. ... FROM table1 JOIN table2  

 

六.union实现Full join

    全连接产生的所有记录（双方匹配记录）在表A和表B。如果没有匹配,则对面将包含null。与差集类似。

 

 
  1. mysql> select * from atable  left join btable  on atable.aid=btable.bid  
  2.     -> union  
  3.     -> select * from atable right join btable on atable.aid=btable.bid;  
  4. +------+-----------+------+-------------+  
  5. | aID  | aNum      | bID  | bName       |  
  6. +------+-----------+------+-------------+  
  7. |    1 | a20050111 |    1 |  2006032401 |  
  8. |    2 | a20050112 |    2 | 2006032402  |  
  9. |    3 | a20050113 |    3 | 2006032403  |  
  10. |    4 | a20050114 |    4 | 2006032404  |  
  11. |    5 | a20050115 | NULL | NULL        |  
  12. | NULL | NULL      |    8 | 2006032408  |  
  13. +------+-----------+------+-------------+  


--------------------------------------------------------------------------------------------------------

 

七.性能优化

  1.显示(explicit) inner join VS 隐式(implicit) inner join

 

 
  1. select * from  
  2. table a inner join table b  
  3. on a.id = b.id;  

VS

 
  1. select a.*, b.*  
  2. from table a, table b  
  3. where a.id = b.id;  

    数据库中比较(10w数据)得之，它们用时几乎相同，第一个是显示的inner join，后一个是隐式的inner join。
2.left join/right join VS inner join
    尽量用inner join.避免 left join 和 null.

 

    在使用left join（或right join）时，应该清楚的知道以下几点：

(1). on与 where的执行顺序
    ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
    所以我们要注意：在使用Left (right) join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行。如：

 
  1. select * from A  
  2. inner join B on B.name = A.name  
  3. left join C on C.name = B.name  
  4. left join D on D.id = C.id  
  5. where C.status>1 and D.status=1;  

下面这种写法更省时

[sql]  view plain  copy
 
  1. select * from A  
  2. inner join B on B.name = A.name  
  3. left join C on C.name = B.name and C.status>1  
  4. left join D on D.id = C.id and D.status=1  

(2).注意ON 子句和 WHERE 子句的不同

[sql]  view plain  copy
 
  1. mysql> SELECT * FROM product LEFT JOIN product_details  
  2.        ON (product.id = product_details.id)  
  3.        AND product_details.id=2;  
  4. +----+--------+------+--------+-------+  
  5. | id | amount | id   | weight | exist |  
  6. +----+--------+------+--------+-------+  
  7. |  1 |    100 | NULL |   NULL |  NULL |  
  8. |  2 |    200 |    2 |     22 |     0 |  
  9. |  3 |    300 | NULL |   NULL |  NULL |  
  10. |  4 |    400 | NULL |   NULL |  NULL |  
  11. +----+--------+------+--------+-------+  
  12. 4 rows in set (0.00 sec)  
  13.    
  14. mysql> SELECT * FROM product LEFT JOIN product_details  
  15.        ON (product.id = product_details.id)  
  16.        WHERE product_details.id=2;  
  17. +----+--------+----+--------+-------+  
  18. | id | amount | id | weight | exist |  
  19. +----+--------+----+--------+-------+  
  20. |  2 |    200 |  2 |     22 |     0 |  
  21. +----+--------+----+--------+-------+  
  22. 1 row in set (0.01 sec)  

    从上可知，第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。
(3).尽量避免子查询，而用join
    往往性能这玩意儿，更多时候体现在数据量比较大的时候，此时，我们应该避免复杂的子查询。如下：

 

 

 
  1. insert into t1(a1) select b1 from t2   
  2. where not exists(select 1 from t1 where t1.id = t2.r_id);  

下面这个更好

 
    1. insert into t1(a1)    
    2. select b1 from t2    
    3. left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id     
    4. where t1.id is null;    

Hive的join操作，left join,right join,inner join

Hive的join操作，left join,right join,inner join

hive执行引擎会将hql转化成mapreduce作业

map side join除外，map负责分发数据，具体的join在reduce端进行

1.如果多表基于不同的列做join,则无法在一轮mapreduce将相关数据shuffle到统一一个reducer,对于多表的join,hive会将前面的表缓存在reducer的内存中，然后后面的表会流式的进入reducer做join.为了防止oom,通常将大表放在最后。

hive中分为map join和common join

common join

map阶段：

读取源表数据，

map输出以join on条件中的列为key,如果join有多个关联键，则以这些关联键的组合作为key.

value为 join之后所关心的列，也会包含tag信息，用于标注value对应哪个表

按key进行排序

shuffle阶段：

根据key进行hash,根据hash推送到不同的reduce中，保证两个表中相同的key在同一个reduce中

reduce阶段：

通过key完成join,通过tag来识别不同表的数据

map join

通常用于一个小表join大表，小表标准由hive.mapjoin.smalltable.filesize决定，设置hive.auto.convert.join为true,小表达到标准会自动转为mapjoin

mapjoin可以看做broadcast join,将小表数据加载到内存，没有shuffle过程，加快处理速度，但是数据量大容易出现oom，

普通join会有shuffle,影响效率，同时会出现数据倾斜

INNER JOIN，LEFT JOIN，RIGHT JOIN和FULL JOIN有什么区别？ [重复]

INNER JOIN，LEFT JOIN，RIGHT JOIN和FULL JOIN有什么区别？ [重复]

问题：

This question already has an answer here: 这个问题已经在这里有了答案：

  • What is the difference between “INNER JOIN” and “OUTER JOIN”? “ INNER JOIN”和“ OUTER JOIN”有什么区别？ 25 answers 25个答案

What''s the difference between INNER JOIN , LEFT JOIN , RIGHT JOIN and FULL JOIN in MySQL ? MySQL中的 INNER JOIN ， LEFT JOIN ， RIGHT JOIN和FULL JOIN什么区别？

解决方案：

参考一： https://stackoom.com/question/NwVJ/INNER-JOIN-LEFT-JOIN-RIGHT-JOIN和FULL-JOIN有什么区别-重复
参考二： https://oldbug.net/q/NwVJ/What-s-the-difference-between-INNER-JOIN-LEFT-JOIN-RIGHT-JOIN-and-FULL-JOIN-duplicate

join连接的五种方式的简单使用案例（Inner join,Left join,Right join,Full join,Cross join）

join连接的五种方式的简单使用案例（Inner join,Left join,Right join,Full join,Cross join）

1.内连接Inner join

内连接是基于连接谓词将俩张表（如A和B）的列组合到一起产生新的结果表，在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

下面是一个简单的使用案例

以下是运行代码及结果

2.左外连接Left join

左外连接Left join关键字会从左表那里返回所有的行，即使是在右表中没有匹配到的行

下面是一个简单的案例

下面是测试用例

3.右外连接Right join

右外连接关键字Right join会从右表那里返回所有的行，即使是在左表中没有匹配到的行

下面是一个简单的案例

下面是运行及其结果

4.全连接Full join

全连接的关键字Full join，只要其中某个表中存在匹配，Full join 就会返回行

 

下面是一个简单的案例

以下是运行及结果

注意一点 mysql中是不支持Full join 的但是orcal等数据库是支持的。如果在mysql要使用Full join就会报以下错误

 

解决办法：同时使用左连接和右连接
以下是一个简单的例子

5.交叉连接

交叉连接一般使用的比较少，交叉连接又称笛卡尔连接或者叉乘连接，如果，A和B是俩个集合，他们的交叉连接就是A*B
以下是一个简单的案例

 

 

关于SQL JOIN和不同类型的JOIN的问题就给大家分享到这里，感谢你花时间阅读本站内容，更多关于(转载) Mysql----Join用法(Inner join,Left join,Right join, Cross join, Union模拟Full join)及---性能优化、Hive的join操作，left join,right join,inner join、INNER JOIN，LEFT JOIN，RIGHT JOIN和FULL JOIN有什么区别？ [重复]、join连接的五种方式的简单使用案例（Inner join,Left join,Right join,Full join,Cross join）等相关知识的信息别忘了在本站进行查找喔。

本文标签：

上一篇SQL Server 2008-拆分（sqlserver2008拆分函数）

下一篇使用不同的SQL语句批处理PreparedStatement

相关文章