join 查询怎么用？

[我咧个去]

如有 《表1 ID 信息1 信息2 信息3 》 《表2 ID》
我想查询 表1ID = 表2ID 的表1 的所有数据
我就打算用 join语句
如 select （什么） from 表1 as a join 表2 as b on a.ID = b.ID 来实现
但是我不知道上面括号里填什么，如果填*号的话它会把表2的数据也显示出来。

如果把表1 的数据依次用 a。ID这种形式填在括号里面倒是能实现想法。
但是我的表1 里面有很多信息列，一次写显得很麻烦，有不有更好的办法，来实现我只想显示表1的数据而不显示表2的数据？

[让你望见影子的墙]

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)

[-晴天]

select a.* from 表1 as a join 表2 as b on a.ID = b.ID

[--小F--]

--这样写:
select a.* from 表1  a join 表2  b on a.ID = b.ID

[ks_reny]

select a.* from 表1  a join 表2  b on a.ID = b.ID ----可以不应as