C. 使用 SQL-92 CROSS JOIN 语法
此示例返回 authors 和 publishers 两个表的矢量积。所返回的列表包含 au_lname 行和所有 pub_name 行的所有可能的组合。USE pubs
SELECT au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname ASC, pub_name ASC下面是结果集：au_lname pub_name
---------------------------------------- -------------------------------
Bennet Algodata Infosystems
Bennet Binnet & Hardley
Bennet Five Lakes Publishing
Bennet GGG&G
Bennet Lucerne Publishing
Bennet New Moon Books
Bennet Ramona Publishers
Bennet Scootney Books
Blotchet-Halls Algodata Infosystems
Blotchet-Halls Binnet & Hardley
Blotchet-Halls Five Lakes Publishing
Blotchet-Halls GGG&G
Blotchet-Halls Lucerne Publishing
Blotchet-Halls New Moon Books
Blotchet-Halls Ramona Publishers
Blotchet-Halls Scootney Books
Carson Algodata Infosystems
Carson Binnet & Hardley
Carson Five Lakes Publishing
...
Stringer Scootney Books
White Algodata Infosystems
White Binnet & Hardley
White Five Lakes Publishing
White GGG&G
White Lucerne Publishing
White New Moon Books
White Ramona Publishers
White Scootney Books
Yokomoto Algodata Infosystems
Yokomoto Binnet & Hardley
Yokomoto Five Lakes Publishing
Yokomoto GGG&G
Yokomoto Lucerne Publishing
Yokomoto New Moon Books
Yokomoto Ramona Publishers
Yokomoto Scootney Books(184 row(s) affected)D. 使用 SQL-92 FULL OUTER JOIN 语法
此示例返回 titles 表中的书籍标题及对应的出版商。还返回未出版列在 titles 表中的书籍的出版商，以及不是由 publishers 表中所列的出版商出版的所有书籍标题。USE pubs
-- The OUTER keyword following the FULL keyword is optional.
SELECT SUBSTRING(titles.title, 1, 10) AS Title,
publishers.pub_name AS Publisher
FROM publishers FULL OUTER JOIN titles
ON titles.pub_id = publishers.pub_id
WHERE titles.pub_id IS NULL
OR publishers.pub_id IS NULL
ORDER BY publishers.pub_name下面是结果集：Title Publisher
---------- ----------------------------------------
NULL Five Lakes Publishing
NULL GGG&G
NULL Lucerne Publishing
NULL Ramona Publishers
NULL Scootney Books(5 row(s) affected)E. 使用 SQL-92 LEFT OUTER JOIN 语法
此示例在 au_id 上联接两个表，并保留左表中没有匹配项的行。authors 表与 titleauthor 表在各表的 au_id 列上相匹配。无论作品出版或未出版，所有作者均出现在结果集中。USE pubs
-- The OUTER keyword following the LEFT keyword is optional.
SELECT SUBSTRING(authors.au_lname, 1, 10) AS Last,
authors.au_fname AS First, titleauthor.title_id
FROM authors LEFT OUTER JOIN titleauthor
ON authors.au_id = titleauthor.au_id下面是结果集：Last First title_id
---------- -------------------- --------
White Johnson PS3333
Green Marjorie BU1032
Green Marjorie BU2075
Carson Cheryl PC1035
... ...
McBadden Heather NULL
Ringer Anne PS2091
Ringer Albert PS2091
Ringer Albert PS2106 (29 row(s) affected)F. 使用 SQL-92 INNER JOIN 语法
此示例返回所有出版商名称以及相应的所出版的书籍标题。USE pubs
-- By default, SQL Server performs an INNER JOIN if only the JOIN
-- keyword is specified.
SELECT SUBSTRING(titles.title, 1, 30) AS Title, publishers.pub_name
FROM publishers INNER JOIN titles
ON titles.pub_id = publishers.pub_id
ORDER BY publishers.pub_name下面是结果集：Title pub_name
------------------------------ ----------------------------------------
The Busy Executive's Database Algodata Infosystems
Cooking with Computers: Surrep Algodata Infosystems
Straight Talk About Computers Algodata Infosystems
But Is It User Friendly? Algodata Infosystems
Secrets of Silicon Valley Algodata Infosystems
Net Etiquette Algodata Infosystems
Silicon Valley Gastronomic Tre Binnet & Hardley
The Gourmet Microwave Binnet & Hardley
The Psychology of Computer Coo Binnet & Hardley
Computer Phobic AND Non-Phobic Binnet & Hardley
Onions, Leeks, and Garlic: Coo Binnet & Hardley
Fifty Years in Buckingham Pala Binnet & Hardley
Sushi, Anyone? Binnet & Hardley
You Can Combat Computer Stress New Moon Books
Is Anger the Enemy? New Moon Books
Life Without Fear New Moon Books
Prolonged Data Deprivation: Fo New Moon Books
Emotional Security: A New Algo New Moon Books (18 row(s) affected)G. 使用 SQL-92 RIGHT OUTER JOIN 语法
此示例在 pub_id 上联接两个表，并保留右表中没有匹配项的行。publishers 表与 titles 表在各表的 pub_id 上相匹配。无论是否已出版书籍，所有出版商均出现在结果集中。USE pubs
SELECT SUBSTRING(titles.title, 1, 30) AS 'Title', publishers.pub_name
FROM titles RIGHT OUTER JOIN publishers
ON titles.pub_id = publishers.pub_id
ORDER BY publishers.pub_name下面是结果集：Title pub_name
------------------------------ ----------------------------------------
The Busy Executive's Database Algodata Infosystems
Cooking with Computers: Surrep Algodata Infosystems
Straight Talk About Computers Algodata Infosystems
But Is It User Friendly? Algodata Infosystems
Secrets of Silicon Valley Algodata Infosystems
Net Etiquette Algodata Infosystems
Silicon Valley Gastronomic Tre Binnet & Hardley
The Gourmet Microwave Binnet & Hardley
The Psychology of Computer Coo Binnet & Hardley
Computer Phobic AND Non-Phobic Binnet & Hardley
Onions, Leeks, and Garlic: Coo Binnet & Hardley
Fifty Years in Buckingham Pala Binnet & Hardley
Sushi, Anyone? Binnet & Hardley
NULL Five Lakes Publishing
NULL GGG&G
NULL Lucerne Publishing
You Can Combat Computer Stress New Moon Books
Is Anger the Enemy? New Moon Books
Life Without Fear New Moon Books
Prolonged Data Deprivation: Fo New Moon Books
Emotional Security: A New Algo New Moon Books
NULL Ramona Publishers
NULL Scootney Books (23 row(s) affected)
此示例返回 authors 和 publishers 两个表的矢量积。所返回的列表包含 au_lname 行和所有 pub_name 行的所有可能的组合。USE pubs
SELECT au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname ASC, pub_name ASC下面是结果集：au_lname pub_name
---------------------------------------- -------------------------------
Bennet Algodata Infosystems
Bennet Binnet & Hardley
Bennet Five Lakes Publishing
Bennet GGG&G
Bennet Lucerne Publishing
Bennet New Moon Books
Bennet Ramona Publishers
Bennet Scootney Books
Blotchet-Halls Algodata Infosystems
Blotchet-Halls Binnet & Hardley
Blotchet-Halls Five Lakes Publishing
Blotchet-Halls GGG&G
Blotchet-Halls Lucerne Publishing
Blotchet-Halls New Moon Books
Blotchet-Halls Ramona Publishers
Blotchet-Halls Scootney Books
Carson Algodata Infosystems
Carson Binnet & Hardley
Carson Five Lakes Publishing
...
Stringer Scootney Books
White Algodata Infosystems
White Binnet & Hardley
White Five Lakes Publishing
White GGG&G
White Lucerne Publishing
White New Moon Books
White Ramona Publishers
White Scootney Books
Yokomoto Algodata Infosystems
Yokomoto Binnet & Hardley
Yokomoto Five Lakes Publishing
Yokomoto GGG&G
Yokomoto Lucerne Publishing
Yokomoto New Moon Books
Yokomoto Ramona Publishers
Yokomoto Scootney Books(184 row(s) affected)D. 使用 SQL-92 FULL OUTER JOIN 语法
此示例返回 titles 表中的书籍标题及对应的出版商。还返回未出版列在 titles 表中的书籍的出版商，以及不是由 publishers 表中所列的出版商出版的所有书籍标题。USE pubs
-- The OUTER keyword following the FULL keyword is optional.
SELECT SUBSTRING(titles.title, 1, 10) AS Title,
publishers.pub_name AS Publisher
FROM publishers FULL OUTER JOIN titles
ON titles.pub_id = publishers.pub_id
WHERE titles.pub_id IS NULL
OR publishers.pub_id IS NULL
ORDER BY publishers.pub_name下面是结果集：Title Publisher
---------- ----------------------------------------
NULL Five Lakes Publishing
NULL GGG&G
NULL Lucerne Publishing
NULL Ramona Publishers
NULL Scootney Books(5 row(s) affected)E. 使用 SQL-92 LEFT OUTER JOIN 语法
此示例在 au_id 上联接两个表，并保留左表中没有匹配项的行。authors 表与 titleauthor 表在各表的 au_id 列上相匹配。无论作品出版或未出版，所有作者均出现在结果集中。USE pubs
-- The OUTER keyword following the LEFT keyword is optional.
SELECT SUBSTRING(authors.au_lname, 1, 10) AS Last,
authors.au_fname AS First, titleauthor.title_id
FROM authors LEFT OUTER JOIN titleauthor
ON authors.au_id = titleauthor.au_id下面是结果集：Last First title_id
---------- -------------------- --------
White Johnson PS3333
Green Marjorie BU1032
Green Marjorie BU2075
Carson Cheryl PC1035
... ...
McBadden Heather NULL
Ringer Anne PS2091
Ringer Albert PS2091
Ringer Albert PS2106 (29 row(s) affected)F. 使用 SQL-92 INNER JOIN 语法
此示例返回所有出版商名称以及相应的所出版的书籍标题。USE pubs
-- By default, SQL Server performs an INNER JOIN if only the JOIN
-- keyword is specified.
SELECT SUBSTRING(titles.title, 1, 30) AS Title, publishers.pub_name
FROM publishers INNER JOIN titles
ON titles.pub_id = publishers.pub_id
ORDER BY publishers.pub_name下面是结果集：Title pub_name
------------------------------ ----------------------------------------
The Busy Executive's Database Algodata Infosystems
Cooking with Computers: Surrep Algodata Infosystems
Straight Talk About Computers Algodata Infosystems
But Is It User Friendly? Algodata Infosystems
Secrets of Silicon Valley Algodata Infosystems
Net Etiquette Algodata Infosystems
Silicon Valley Gastronomic Tre Binnet & Hardley
The Gourmet Microwave Binnet & Hardley
The Psychology of Computer Coo Binnet & Hardley
Computer Phobic AND Non-Phobic Binnet & Hardley
Onions, Leeks, and Garlic: Coo Binnet & Hardley
Fifty Years in Buckingham Pala Binnet & Hardley
Sushi, Anyone? Binnet & Hardley
You Can Combat Computer Stress New Moon Books
Is Anger the Enemy? New Moon Books
Life Without Fear New Moon Books
Prolonged Data Deprivation: Fo New Moon Books
Emotional Security: A New Algo New Moon Books (18 row(s) affected)G. 使用 SQL-92 RIGHT OUTER JOIN 语法
此示例在 pub_id 上联接两个表，并保留右表中没有匹配项的行。publishers 表与 titles 表在各表的 pub_id 上相匹配。无论是否已出版书籍，所有出版商均出现在结果集中。USE pubs
SELECT SUBSTRING(titles.title, 1, 30) AS 'Title', publishers.pub_name
FROM titles RIGHT OUTER JOIN publishers
ON titles.pub_id = publishers.pub_id
ORDER BY publishers.pub_name下面是结果集：Title pub_name
------------------------------ ----------------------------------------
The Busy Executive's Database Algodata Infosystems
Cooking with Computers: Surrep Algodata Infosystems
Straight Talk About Computers Algodata Infosystems
But Is It User Friendly? Algodata Infosystems
Secrets of Silicon Valley Algodata Infosystems
Net Etiquette Algodata Infosystems
Silicon Valley Gastronomic Tre Binnet & Hardley
The Gourmet Microwave Binnet & Hardley
The Psychology of Computer Coo Binnet & Hardley
Computer Phobic AND Non-Phobic Binnet & Hardley
Onions, Leeks, and Garlic: Coo Binnet & Hardley
Fifty Years in Buckingham Pala Binnet & Hardley
Sushi, Anyone? Binnet & Hardley
NULL Five Lakes Publishing
NULL GGG&G
NULL Lucerne Publishing
You Can Combat Computer Stress New Moon Books
Is Anger the Enemy? New Moon Books
Life Without Fear New Moon Books
Prolonged Data Deprivation: Fo New Moon Books
Emotional Security: A New Algo New Moon Books
NULL Ramona Publishers
NULL Scootney Books (23 row(s) affected)
select a.* from 表1 a join 表2 b on a.ID = b.ID
select a.* from 表1 a join 表2 b on a.ID = b.ID ----可以不应as