什么叫左连接，右连接，子表？

时间：2009-01-02 来源：militala

什么叫左连接，右连接，子表？

LEFT JOIN 或 LEFT OUTER JOIN。
左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

RIGHT JOIN 或 RIGHT OUTER JOIN。
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
Top

2 楼yoki（小马哥--鬓微霜，又何妨）回复于 2003-11-07 17:55:11 得分 0

你说的子表是指子查询吧？？
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
如上面的语句中第二个select 。。。为子查询Top

3 楼lvltt（未完成）回复于 2003-11-07 17:57:00 得分 10

Join用法：

declare @a table(a int,b int)
declare @b table(a int,b int)
insert @a values(1,1)
insert @a values(2,2)
insert @b values(1,1)
insert @b values(3,3)

--左:
select * from @a Aa left join @b Bb on Aa.a=Bb.a
--右：
select * from @a Aa right join @b Bb on Aa.a=Bb.a
--内
select * from @a Aa join @b Bb on Aa.a=Bb.a
--外
select * from @a Aa full join @b Bb on Aa.a=Bb.a
--完全
select * from @a,@b

left join 第一张表的连接列在第二张表中没有对应的,第二张表中的值返回null
right join 第二张表的连接列在第一张表中没有对应的,第一张表中的值返回null
full join 返回两张表中的 left join+right join中不相同的记录
inner join 只返回两张表连接列的匹配项Top

4 楼wzh1215（懒猫）回复于 2003-11-07 17:58:38 得分 0

搜索一下旧贴，相关的内容有很多！Top

5 楼Rotaxe（程序员）回复于 2003-11-07 18:15:07 得分 80

仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行，只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。

Microsoft® SQL Server™ 2000 对在 FROM 子句中指定的外联接使用以下 SQL-92 关键字：

LEFT OUTER JOIN 或 LEFT JOIN

RIGHT OUTER JOIN 或 RIGHT JOIN

FULL OUTER JOIN 或 FULL JOIN
SQL Server 支持 SQL-92 外联接语法，以及在 WHERE 子句中使用 *= 和 =* 运算符指定外联接的旧式语法。由于 SQL-92 语法不容易产生歧义，而旧式 Transact-SQL 外联接有时会产生歧义，因此建议使用 SQL-92 语法。

使用左向外联接
假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者（本例中为 Abraham Bennet 和 Cheryl Carson）。

若要在结果中包括所有的作者，而不管出版商是否住在同一个城市，请使用 SQL-92 左向外联接。下面是 Transact-SQL 左向外联接的查询：

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

不管是否与 publishers 表中的 city 列匹配，LEFT OUTER JOIN 均会在结果中包含 authors 表的所有行。注意：结果中所列的大多数作者都没有相匹配的数据,因此，这些行的 pub_name 列包含空值。

使用右向外联接
假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者（本例中为 Abraham Bennet 和 Cheryl Carson）。SQL-92 右向外联接运算符 RIGHT OUTER JOIN 指明：不管第一个表中是否有匹配的数据，结果将包含第二个表中的所有行。

若要在结果中包括所有的出版商，而不管城市中是否还有出版商居住，请使用 SQL-92 右向外联接。下面是 Transact-SQL 右向外联接的查询：

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

一对多关系是最常见的关系类型。在这种关系类型中，表 A 中的行可以在表 B 中有许多匹配行，但是表 B 中的行只能在表 A 中有一个匹配行。例如，publishers 表和 titles 表是一对多的关系：每一个出版商可出版许多书，但每一本书只能有一个出版商。
通常publishers 表叫父表或主表，titles 表叫子表或从表
Top

6 楼manlian（新英战舰）回复于 2003-11-07 21:45:39 得分 0

7 楼pepos（风声雨声读书声我不出声家事国事天下事关我鸟事）回复于 2003-11-07 22:09:08 得分 0

--左:
select * from @a Aa left join @b Bb on Aa.a=Bb.a
--右：
select * from @a Aa right join @b Bb on Aa.a=Bb.a
--内
select * from @a Aa join @b Bb on Aa.a=Bb.a
--外
select * from @a Aa full join @b Bb on Aa.a=Bb.a
--完全
select * from @a,@b

连接无非是这几个
--内连接和where相同
inner join
--左向外连接，返回左边表所有符合条件的
left join
--右向外连接，返回右边表所有符合条件的
right join
--完整外部连接，左向外连接和右向外连接的合集
full join
--交叉连接，也称笛卡儿积。返回左表中的每一行与右表中所有行的组合
cross join
Top

8 楼CSFish（海里唯一的鱼）回复于 2003-11-07 22:39:45 得分 0

9 楼happydreamer（www.sz.js.cn,www.gyxk.com）回复于 2003-11-08 00:01:01 得分 0

关系运算：

并运算
select c1,c2 from t1
union all
select c1,c2 from t2

差：
c1-c2:

select * from t1 where not exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)

c2-c1:

select * from t2 where not exists(select 1 from t1 where t1.c1=t2.c1 and t1.c2=t2.c2)

交:
select * from t1 where exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)

用left join来着差运算

create table #a(a int,b int)
create table #b(a int,b int)
insert #a values(1,1)
insert #a values(2,2)
insert #b values(1,1)
insert #b values(3,3)

#a-#b
select a.*
from #a a left join #b b on a.a=b.a and a.b=b.b
where b.a is null

#b-#a
select b.*
from #b b left join #a a on a.a=b.a and a.b=b.b
where a.a is null

or:
select b.*
from #a a right join #b b on a.a=b.a and a.b=b.b
where a.a is nullTop

10 楼jastion（嘉思）回复于 2003-11-08 09:02:03 得分 0

-----------------------------
-- 第一章 - T-SQL 中的聯結 --
-----------------------------

-- 程式 1-1: 建立人力資源資料庫結構的指令碼
CREATE TABLE Departments
(
Deptno int NOT NULL
CONSTRAINT PK_dept_deptno PRIMARY KEY,
deptname varchar(15) NOT NULL
)

CREATE TABLE Jobs
(
jobid int NOT NULL
CONSTRAINT PK_jobs_jobid PRIMARY KEY,
jobdesc varchar(15) NOT NULL
)

CREATE TABLE Employees
(
empid int NOT NULL
CONSTRAINT PK_emps_empid PRIMARY KEY,
empname varchar(10) NOT NULL,
deptno int NULL
CONSTRAINT FK_emps_depts
REFERENCES Departments(deptno),
jobid int NOT NULL
CONSTRAINT FK_emps_jobs REFERENCES Jobs(jobid),
salary decimal(7,2) NOT NULL
)

INSERT INTO Departments VALUES(100, 'Engineering')
INSERT INTO Departments VALUES(200, 'Production')
INSERT INTO Departments VALUES(300, 'Sanitation')
INSERT INTO Departments VALUES(400, 'Management')

INSERT INTO Jobs VALUES(10, 'Engineer')
INSERT INTO Jobs VALUES(20, 'Worker')
INSERT INTO Jobs VALUES(30, 'Manager')
INSERT INTO Jobs VALUES(40, 'Cleaner')

INSERT INTO Employees VALUES(1, 'Leo', 400, 30, 10000.00)
INSERT INTO Employees VALUES(2, 'George', 200, 20, 1000.00)
INSERT INTO Employees VALUES(3, 'Chris', 100, 10, 2000.00)
INSERT INTO Employees VALUES(4, 'Rob', 400, 30, 3000.00)
INSERT INTO Employees VALUES(5, 'Laura', 400, 30, 3000.00)
INSERT INTO Employees VALUES(6, 'Jeffrey', NULL, 30, 5000.00)

-- 程式 1-2: 簡短形式的 SELECT 陳述式
SELECT
<select_list>
FROM
<table_source>
[WHERE
<search_condition>]

-- 程式 1-3: SQL-89 的聯結語法
SELECT
<select_list>
FROM
T1, T2
WHERE
<join_condition> [AND <filter>]

-- 程式 1-4: 舊式雙向 (two-way) 內部聯結
SELECT
empid,
empname,
salary,
E.deptno,
deptname
FROM
Employees AS E,
Departments AS D
WHERE
E.deptno = D.deptno

-- 程式 1-5: SQL-92 聯結語法
SELECT
<select_list>
FROM
T1
<join_type> JOIN
T2 [ON <join_condition>]
[<join_type> JOIN
T3 [ON <join_condition>]
[WHERE
<filter>]

-- 程式 1-6: SQL-92 雙向內部聯結的簡短形式
SELECT
<select_list>
FROM
T1
[INNER] JOIN
T2 ON <join_condition>
[WHERE
<filter>]

-- 程式 1-7: SQL-92 雙向內部聯結
SELECT
empid,
empname,
salary,
E.deptno,
deptname
FROM
Employees AS E
JOIN
Departments AS D ON E.deptno = D.deptno

-- 程式 1-8: 舊式三向 (three-way) 內部聯結
SELECT
empid,
empname,
salary,
E.deptno,
deptname,
E.jobid,
jobdesc
FROM
Employees AS E,
Departments AS D,
Jobs AS J
WHERE
E.deptno = D.deptno
AND
E.jobid = J.jobid

-- 程式 1-9: SQL-92 三向內部聯結
SELECT
empid,
empname,
salary,
E.deptno,
deptname,
E.jobid,
jobdesc
FROM
Employees AS E
JOIN
Departments AS D ON E.deptno = D.deptno
JOIN
Jobs AS J ON E.jobid = J.jobid

-- 程式 1-10: 強制聯結處理的順序
SELECT
empid,
empname,
salary,
E.deptno,
deptname,
E.jobid,
jobdesc
FROM
Employees AS E
JOIN
Departments AS D ON E.deptno = D.deptno
JOIN
Jobs AS J ON E.jobid = J.jobid
OPTION(FORCE ORDER)

-- 程式 1-11: 舊式交叉聯結語法
SELECT
deptname,
jobdesc
FROM
Departments,
Jobs

-- 程式 1-12: SQL-92 的交叉聯結語法
SELECT
deptname,
jobdesc
FROM
Departments
CROSS JOIN
Jobs

-- 程式 1-13: 舊式外部聯結語法
SELECT
<select_list>
FROM
T1,
T2
WHERE
T1.key_col {*= | =*} T2.key_col [AND <filter>]

-- 程式 1-14: 舊式雙向左外部聯結
SELECT
*
FROM
Employees AS E,
Departments AS D
WHERE
E.deptno *= D.deptno

-- 程式 1-15: SQL-92 雙向外部聯結語法
SELECT
<select_list>
FROM
T1
{LEFT | RIGHT | FULL} [OUTER] JOIN
T2 ON <join_condition>
[WHERE
<filter>]

-- 程式 1-16: SQL-92 雙向左外部聯結
SELECT
*
FROM
Employees AS E
LEFT OUTER JOIN
Departments AS D ON E.deptno = D.deptno

-- 程式 1-17: 舊式雙向右外部聯結
SELECT
*
FROM
Employees AS E,
Departments AS D
WHERE
E.deptno =* D.deptno

-- 程式 1-18: SQL-92 雙向右外部聯結
SELECT
*
FROM
Employees AS E
RIGHT OUTER JOIN
Departments AS D ON E.deptno = D.deptnoTop

11 楼jastion（嘉思）回复于 2003-11-08 09:02:29 得分 0

-- 程式 1-19: SQL-92 雙向完整外部聯結
SELECT
*
FROM
Employees AS E
FULL OUTER JOIN
Departments AS D ON E.deptno = D.deptno

-- 程式 1-20: SQL-92 三向外部聯結 -- 範例一
SELECT
*
FROM
Employees AS E
LEFT OUTER JOIN
Departments AS D ON E.deptno = D.deptno
RIGHT OUTER JOIN
Jobs AS J ON E.jobid = J.jobid

-- 程式 1-21: 違規的舊式三向外部聯結 -- 範例一
SELECT
*
FROM
Employees AS E,
Departments AS D,
Jobs AS J
WHERE
E.deptno *= D.deptno
AND
E.jobid =* J.jobid

-- 程式 1-22: SQL-92 三向外部聯結 -- 範例二
SELECT
*
FROM
Employees AS E
JOIN
Departments AS D ON E.deptno = D.deptno
RIGHT OUTER JOIN
Jobs AS J ON E.jobid = J.jobid

-- 程式 1-23: 違規的舊式三向外部聯結 -- 範例二
SELECT
*
FROM
Employees AS E,
Departments AS D,
Jobs AS J
WHERE
E.deptno = D.deptno
AND
E.jobid =* J.jobid

-- 程式 1-24: Departments 與 Employees 資料表的舊式左外部聯結，保留所有部門資料
SELECT
*
FROM
Departments AS D,
Employees AS E
WHERE
D.deptno *= E.deptno

-- 程式 1-25: 使用舊式語法尋找沒有職員的部門
SELECT
*
FROM
Departments AS D,
Employees AS E
WHERE
D.deptno *= E.deptno
AND
E.deptno IS NULL

-- 程式 1-26: 舊式外部連結的執行計劃
3 |--Nested Loops(Left Outer Join, WHERE:([D].[deptno]=NULL))
1 |--Clustered Index Scan(OBJECT:([testdb].[dbo].[Departments].[PK_dept_deptno] AS [D]))
2 |--Clustered Index Scan(OBJECT:([testdb].[dbo].[Employees].[PK_emps_empid] AS [E]), WHERE:([E].[deptno]=NULL))

-- 程式 1-27: 使用 SQL-92 語法尋找沒有職員的部門
SELECT
*
FROM
Departments AS D
LEFT OUTER JOIN
Employees AS E ON D.deptno = E.deptno
WHERE
E.deptno IS NULL

-- 程式 1-28: 使用 SQL-92 語法進行外部聯結的執行計劃
4 |--Filter(WHERE:([E].[deptno]=NULL))
3 |--Nested Loops(Left Outer Join, WHERE:([D].[deptno]=[E].[deptno]))
1 |--Clustered Index Scan(OBJECT:([testdb].[dbo].[Departments].[PK_dept_deptno] AS [D]))
2 |--Clustered Index Scan(OBJECT:([testdb].[dbo].[Employees].[PK_emps_empid] AS [E]))

-- 程式 1-29: 控制聯結與篩選資料列的順序
SELECT
*
FROM
Departments AS D
LEFT OUTER JOIN
Employees AS E ON D.deptno = E.deptno
AND E.deptno IS NULL

-- 程式 1-30: 建立 Candidates 資料表結構的指令碼
CREATE TABLE Candidates
(
candname varchar(10) NOT NULL,
gender char(1) NOT NULL
CONSTRAINT CHK_gender
CHECK (gender IN('F', 'M'))
)

INSERT INTO Candidates VALUES('Neil' , 'M')
INSERT INTO Candidates VALUES('Trevor' , 'M')
INSERT INTO Candidates VALUES('Terresa', 'F')
INSERT INTO Candidates VALUES('Mary' , 'F')

-- 程式 1-31: 使用交叉聯結進行配對 -- 所有可能配對
SELECT
T1.candname,
T2.candname
FROM
Candidates AS T1
CROSS JOIN
Candidates AS T2

-- 程式 1-32: 使用交叉聯結進行配對 -- 配對的名字不可相同
SELECT
T1.candname,
T2.candname
FROM
Candidates AS T1
CROSS JOIN
Candidates AS T2
WHERE
T1.candname <> T2.candname

-- 程式 1-33: 使用交叉聯結進行配對 -- 配對的性別不可相同
SELECT
T1.candname,
T2.candname
FROM
Candidates AS T1
CROSS JOIN
Candidates AS T2
WHERE
T1.gender <> T2.gender

-- 程式 1-34: 使用交叉聯結進行配對 -- 完整的查詢
SELECT
M.candname AS Guy,
F.candname AS Girl
FROM
Candidates AS M
CROSS JOIN
Candidates AS F
WHERE
M.gender <> F.gender
AND
M.gender = 'M'

-- 程式 1-35: 使用交叉聯結進行配對 -- 最小條件
SELECT
M.candname AS Guy,
F.candname AS Girl
FROM
Candidates AS M
CROSS JOIN
Candidates AS F
WHERE
M.gender > F.gender

-- 程式 1-36: 使用內部聯結進行配對
SELECT
M.candname AS Guy,
F.candname AS Girl
FROM
Candidates AS M
JOIN
Candidates AS F ON M.gender > F.gender

-- 程式 1-37: 建立 Salarylevels 資料表結構的指令碼
CREATE TABLE Salarylevels
(
lowbound decimal(7,2) NOT NULL,
highbound decimal(7,2) NOT NULL,
sallevel varchar(50) NOT NULL
)

INSERT INTO Salarylevels
VALUES(0.00, 1500.00, 'Doing most of the work')
INSERT INTO Salarylevels
VALUES(1500.01, 2500.00, 'Planning the work')
INSERT INTO Salarylevels
VALUES(2500.01, 4500.00, 'Tell subordinates what to do')
INSERT INTO Salarylevels
VALUES(4500.01, 99999.99, 'Owners and their relatives')

-- 程式 1-38: 在聯結條件裡使用述詞 BETWEEN
SELECT
E.*,
sallevel
FROM
Employees AS E
JOIN
Salarylevels AS SL ON E.salary BETWEEN lowbound AND highbound

-- 程式 1-39: 搭配聯結的 DELETE 語法
DELETE [FROM] <modified_table>
[FROM
<modified_table>
<join_type> JOIN
<another_table> ON <join_condition>]
[WHERE
<search_condition>]

-- 程式 1-40: 搭配聯結的 DELETE
DELETE FROM [Order Details]
FROM
[Order Details] AS OD
JOIN
Orders AS O ON OD.orderid = O.orderid
WHERE
CustomerID = 'VINET'

-- 程式 1-41: 搭配 JOIN 的 UPDATE 語法
UPDATE <modified_table>
SET col1 = <new_value>[,
col2 = <new_value>]
[FROM
<modified_table>
<join_type> JOIN
<another_table> ON <join_condition>]
[WHERE
<search_condition>]

-- 程式 1-42: 搭配 JOIN 的 UPDATE
UPDATE OD
SET Discount = Discount + 0.05
FROM
[Order Details] AS OD
JOIN
Products AS P ON OD.productid = P.productid
WHERE
SupplierID = 1

-- 程式 1-43: 不完全符合 (Incompletely Qualified) 的篩選條件形成的查詢
SELECT
*
FROM
Orders AS O
JOIN
[Order Details] AS OD ON OD.OrderID = O.OrderID
WHERE
O.OrderID >= 11000

-- 程式 1-44: 不完全符合的篩選條件所形成查詢的 I/O Measures
Table 'Order Details'. Scan count 78, logical reads 158, physical reads 0, read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

-- 程式 1-45: 不完全符合的篩選條件所形成查詢的執行計劃
|--Nested Loops(Inner Join, OUTER REFERENCES:([O].[OrderID]))
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders] AS [O]), SEEK:([O].[OrderID] >= 11000) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Order Details].[PK_Order_Details] AS [OD]), SEEK:([OD].[OrderID]=[O].[OrderID]) ORDERED FORWARD)

-- 程式 1-46: 完全符合的篩選條件所形成的查詢
SELECT
*
FROM
Orders AS O
JOIN
[Order Details] AS OD ON OD.OrderID = O.OrderID
WHERE
O.OrderID >= 11000
AND
OD.OrderID >= 11000

-- 程式 1-47: 完全符合的篩選條件所形成查詢的 I/O Measures
Table 'Order Details'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

-- 程式 1-48: 完全符合的篩選條件所形成查詢的 SHOWPLAN
|--Merge Join(Inner Join, MERGE:([O].[OrderID])=([OD].[OrderID]), RESIDUAL:([O].[OrderID]=[OD].[OrderID]))
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders] AS [O]), SEEK:([O].[OrderID] >= 11000) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Order Details].[PK_Order_Details] AS [OD]), SEEK:([OD].[OrderID] >= 11000) ORDERED FORWARD)

-- 程式 1-49: 聯結提示的語法
SELECT
<select_list>
FROM
T1
<join_type> <join_hint> JOIN
T2

-- SQL 難題選粹 1-聯結
SELECT
*
FROM
Departments AS D
LEFT OUTER JOIN
Employees AS E ON D.deptno = E.deptno
WHERE
D.deptno IN(300, 400)
AND
E.salary > 2500.00

SELECT
*
FROM
Departments AS D
LEFT OUTER JOIN
Employees AS E ON D.deptno = E.deptno
AND E.salary > 2500.00
WHERE
D.deptno IN(300, 400)