语法
join 用于根据两个或多个表中的列之间的关系,从这些表中查询数据。
Join 和 Key
有时为了得到完整的结果,我们需要从两个或更多的表中获取结果。我们就需要执行 join。
数据库中的表可通过键将彼此联系起来。主键(Primary Key)是一个列,在这个列中的每一行的值都是唯一的。在表中,每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下,把表间的数据交叉捆绑在一起。
请看 "Persons" 表:
Id_P | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
请注意,"Id_P" 列是 Persons 表中的的主键。这意味着没有两行能够拥有相同的 Id_P。即使两个人的姓名完全相同,Id_P 也可以区分他们。
接下来请看 "Orders" 表:
Id_O | OrderNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
5 | 34764 | 65 |
请注意,"Id_O" 列是 Orders 表中的的主键,同时,"Orders" 表中的 "Id_P" 列用于引用 "Persons" 表中的人,而无需使用他们的确切姓名。
请留意,"Id_P" 列把上面的两个表联系了起来。
引用两个表
我们可以通过引用两个表的方式,从两个表中获取数据:
谁订购了产品,并且他们订购了什么产品?
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P
结果集:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
SQL JOIN - 使用 Join
除了上面的方法,我们也可以使用关键词 JOIN 来从两个表中获取数据。
如果我们希望列出所有人的定购,可以使用下面的 SELECT 语句:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM PersonsINNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
结果集:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
不同的 SQL JOIN
除了我们在上面的例子中使用的 INNER JOIN(内连接),我们还可以使用其他几种连接。
下面列出了您可以使用的 JOIN 类型,以及它们之间的差异。
- JOIN: 如果表中有至少一个匹配,则返回行
- LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
- RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
- FULL JOIN: 只要其中一个表中存在匹配,就返回行
之前例子是inner join 现在展示 left join 和 full join
left join
"Persons" 表:
Id_P | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
"Orders" 表:
Id_O | OrderNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
5 | 34764 | 65 |
左连接(LEFT JOIN)实例
现在,我们希望列出所有的人,以及他们的定购 - 如果有的话。
您可以使用下面的 SELECT 语句:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果集:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
Bush | George |
LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行,即使在右表 (Orders) 中没有匹配的行。
full join
原始的表 (用在例子中的):
"Persons" 表:
Id_P | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
"Orders" 表:
Id_O | OrderNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
5 | 34764 | 65 |
全连接(FULL JOIN)实例
现在,我们希望列出所有的人,以及他们的定单,以及所有的定单,以及定购它们的人。
您可以使用下面的 SELECT 语句:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果集:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
Bush | George | |
34764 |
FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配,或者如果 "Orders" 中的行在表 "Persons" 中没有匹配,这些行同样会列出。
join 中的 where 和 and 区别
在使用left join时,on and和on where条件的区别如下:
1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉,on后的条件用来生成左右表关联的临时表,where后的条件对临时表中的记录进行过滤。
表结构
SQL> create table A (id int, type int);
SQL> select * from A;
ID TYPE
---------- ----------
1 1
2 1
3 2 SQL> create table B(id int ,class int);
SQL> select * from B; ID CLASS
---------- ----------
1 1
2 2
不使用条件
SQL> select * from A left join B on A.id = b.id; ID TYPE ID CLASS
---------- ---------- ---------- ----------
1 1 1 1
2 1 2 2
3 2
使用条件where
SQL> select * from A left join B on A.id = B.id where A.type = 1; ID TYPE ID CLASS
---------- ---------- ---------- ----------
1 1 1 1
2 1 2 2
根据上面那段话的解释,where字句是在生成临时表以后再进行过滤的,也就是可以理解为就是一个左连接:select * from A left join B on A.id = B.id,然后加上where A.type = 1对临时表进行过滤,除掉A.type不为1的数据。
使用and
SQL> select * from A left join B on A.id = B.id and A.type = 1; ID TYPE ID CLASS
---------- ---------- ---------- ----------
1 1 1 1
2 1 2 2
3 2
因为左连接不管on and语句是否为真都必须返回左表所有的记录,所以and A.type=1;没有起到任何作用。
SQL> select * from A left join B on A.id = B.id and B.class = 1; ID TYPE ID CLASS
---------- ---------- ---------- ----------
1 1 1 1
3 2
2 1
根据上面那段话的解释:on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。显然左连接再加上新的条件:B.class = 1筛选掉第二行记录,结果正确。
SQL> select * from A left join B on A.id = B.id where B.class = 1; ID TYPE ID CLASS
---------- ---------- ---------- ----------
1 1 1 1
where是生成临时表以后再进行过滤,对左右表都进行筛选。如果是and,先不管过滤规则,先把左边表数据全部返回,然户过滤规则只对右表产生过滤。
再来看看内连接inner join on and和 on where的区别:
在使用inner join时,不管是对左表还是右表进行筛选,on and和on where都会对生成的临时表左右两边同时进行过滤。
表结构
SQL> select * from A; ID TYPE
---------- ----------
1 2
2 1
3 2 SQL> select * from B; ID CLASS
---------- ----------
1 1
2 2
inner join 的特性就是只返回有匹配的
SQL> select * from A inner join B on A.id = B.id; ID TYPE ID CLASS
---------- ---------- ---------- ----------
1 2 1 1
2 1 2 2
SQL> select * from A inner join B on A.id = B.id where A.type = 1; ID TYPE ID CLASS
---------- ---------- ---------- ----------
2 1 2 2
SQL> select * from A inner join B on A.id = B.id and B.class = 1; ID TYPE ID CLASS
---------- ---------- ---------- ----------
1 2 1 1
SQL> select * from A inner join B on A.id = B.id where B.class = 1; ID TYPE ID CLASS
---------- ---------- ---------- ----------
1 2 1 1
发现会对左右两边同时过滤。
个人使用join和单纯使用where关联表查询
个人测试两个oracle语句
select r.role from user_role ur,Shiro_Role r,shiro_user su where r.id = ur.role_id and ur.user_id = su.id and su.username='cheng'
select r.role from Shiro_Role r inner join user_role ur on ur.role_id = r.id inner join shiro_user su on su.id = ur.user_id and su.username='cheng';
不使用join第一次查询为0.329 重复查询后为0.042到0.026
使用join第一次查询为0.042 重复后为0.042到0.005