GVKun编程网logo

sql join连接3个表（sql连接三个表）

24

本篇文章给大家谈谈sqljoin连接3个表，以及sql连接三个表的知识点，同时本文还将给你拓展(转载)Mysql----Join用法(Innerjoin,Leftjoin,Rightjoin,Cros

本篇文章给大家谈谈sql join连接3个表，以及sql连接三个表的知识点，同时本文还将给你拓展(转载) Mysql----Join用法(Inner join,Left join,Right join, Cross join, Union模拟Full join)及---性能优化、(转载)SQL Server ——用 join on 连接多个表、Hibernate命名查询-联接3个表、Hive join连接问题等相关知识，希望对各位有所帮助，不要忘了收藏本站喔。

本文目录一览：

sql join连接3个表（sql连接三个表）

sql join连接3个表（sql连接三个表）

这个有点奇怪

有人写了一个我认为不起作用的sql，但它确实可以，而且还返回了正确的结果。我已经写了一个简化的示例，但我认为它说明了这一点。

drop table #client;
drop table #transactions;
drop table #history;

create table #client (
    clientId int,name varchar(50)
);
create table #transactions (
    transid int,clientId int,Amount int
);
create table #history (
    transid int,Amount int
);

insert into #client values (1,'User 1');
insert into #client values (2,'User 2');
insert into #client values (3,'User 3');

insert into #transactions values (1,1,50);
insert into #transactions values (2,35);
insert into #transactions values (3,25);

insert into #transactions values (4,2,10);
insert into #transactions values (5,50);
insert into #transactions values (6,35);
insert into #transactions values (7,3,25);
insert into #transactions values (8,10);

insert into #history values (1,50);
insert into #history values (2,35);
insert into #history values (3,25);
insert into #history values (4,10);
insert into #history values (5,50);
insert into #history values (6,35);
insert into #history values (7,25);
insert into #history values (8,10);

select * from #history
join #transactions on #history.transid = #transactions.transid
join #client on #transactions.clientId = #client.clientId and #history.transid = #transactions.transid

最后一个联接将3个表联接在一起，这在我对编写SQL的了解中是行不通的。我错了吗（可能是这样）

(转载) 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;    

(转载)SQL Server ——用 join on 连接多个表

(转载)SQL Server ——用 join on 连接多个表

select * from table1 inner join table2 on table1.id=table2.id
其实 INNER JOIN ……ON的语法格式可以概括为：
FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表X ON Member.字段号=表X.字段号
您只要套用该格式就可以了。

连接两个数据表的用法：
FROM Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort
语法格式可以概括为：
FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

连接三个数据表的用法：
FROM (Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel
语法格式可以概括为：
FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

连接四个数据表的用法：
FROM ((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity
语法格式可以概括为：
FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号

连接五个数据表的用法：
FROM (((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity) INNER JOIN Wedlock ON Member.Wedlock=Wedlock.Wedlock
语法格式可以概括为：
FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号

Hibernate命名查询-联接3个表

Hibernate命名查询-联接3个表

我有3个bean：组织，角色，用户

角色-组织关系-@ManyToOne

角色-用户关系-@ManyToMany

组织机构：

    @Entity
    @Table(name = "entity_organization")
    public class Organization implements Serializable {

        private static final long serialVersionUID = -646783073824774092L;

        @Id
        @GeneratedValue(strategy = GenerationType.TABLE)
        Long id;

        String name;

        @OneToMany(targetEntity = Role.class,mappedBy = "organization")
        List<Role> roleList;

...

角色：

    @Entity
    @Table(name = "entity_role")
    public class Role implements Serializable {

        private static final long serialVersionUID = -8468851370626652688L;

        @Id
        @GeneratedValue(strategy = GenerationType.TABLE)
        Long id;

        String name;

        String description;

        @ManyToOne
        Organization organization;

...

用户名：

    @Entity
    @Table(name = "entity_user")
    public class User implements Serializable {

        private static final long serialVersionUID = -4353850485035153638L;

        @Id
        @GeneratedValue(strategy = GenerationType.TABLE)
        Long id;
        @ManyToMany
        @JoinTable(name = "entity_user_role",joinColumns = @JoinColumn(name = "user_id",referencedColumnName = "id"),inverseJoinColumns = @JoinColumn(name = "role_id",referencedColumnName =                     "id"))
        List<Role> roleList;

...

因此，我需要获取指定用户的所有组织（首先，我需要选择所有用户角色，然后选择具有此角色的所有组织）

我有一个实现此逻辑的sql语句（例如，我选择id = 1的用户）：

SELECT * FROM entity_organization AS o 
INNER JOIN entity_role r ON r.organization_id = o.id 
INNER JOIN entity_user_role ur ON ur.role_id=r.id 
WHERE ur.user_id = 1

如何使用hibernate命名查询机制实现此目的？谢谢！

Hive join连接问题

Hive join连接问题

  1. hive 的join只支持等值连接，并且只能用and，不可以使用or
  2. left semi on： 左半连接，右表的过滤只能在join条件中，而且右表中的列不能出现在select和where中，也就是说，左半连接只能显示左表中的数据

关于sql join连接3个表和sql连接三个表的介绍现已完结，谢谢您的耐心阅读，如果想了解更多关于(转载) Mysql----Join用法(Inner join,Left join,Right join, Cross join, Union模拟Full join)及---性能优化、(转载)SQL Server ——用 join on 连接多个表、Hibernate命名查询-联接3个表、Hive join连接问题的相关知识，请在本站寻找。

本文标签：

上一篇从SQL Server中的JOIN结果获取不同的行

下一篇MySQL-为每个重复值获取一个计数器（mysql 重复数据每个取第一条）

相关文章