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

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

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,@bleft join 第一张表的连接列在第二张表中没有对应的,第二张表中的值返回null
right join 第二张表的连接列在第一张表中没有对应的,第一张表中的值返回null
full join 返回两张表中的 left join+right join中不相同的记录
inner join 只返回两张表连接列的匹配项

仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 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 表叫子表或从表

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

--左:
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

LEFT JOIN 或 LEFT OUTER JOIN。
左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。RIGHT JOIN 或 RIGHT OUTER JOIN。
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
sql server 2000还有
Left Semi Join,Left Anti Semi Join
RightSemi Join,rightAnti Semi Join

关系运算：并运算
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 nullor:
select b.*
from #a a right  join #b b on a.a=b.a and a.b=b.b
where a.a is null

-----------------------------
-- 第一章 - 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.deptno

-- 程式 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.00SELECT
  *
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)

调试易

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

解决方案 »