The Select List in Joins

A join statement, like other selection statements, starts with the keyword SELECT. The columns named after the SELECT keyword are the columns to be included in the query results, in their desired order.

In the example, the columns au_ fname, au_lname, and pub_name were not qualified by a table name since there is no ambiguity about the table to which they belong. But the city column used for the join comparison was qualified since there are columns of that name in both the publishers and authors tables. Even though neither of the city columns is printed in the results, SQL Server needs the table name to perform the comparison.

As in other SELECT statements, you can use * to specify that all columns of the tables involved in the query are to be included in the results. For example, to include all columns in publishers and authors in the preceding join query, use this SQL statement:

SELECT *
FROM authors, publishers
WHERE authors.city = publishers.city
au_id
au_lname
au_fname
phone
address
city
state
---------
--------
--------
-------
-------
--------
-----
238-95-7766
Carson
Cheryl
415 548-7723 
589 Darwin Ln.
Berkeley
CA







409-56-7008
Bennet
Abraham
415 658-9932
6223 Bateman St.
Berkeley
CA


zip
contract
pub_id
pub_name
city
state
country
-----
--------
------
--------
--------
-----
-------
94705
1
1389
Algodata Infosystems
Berkeley
CA
USA







94705
1
1389
Algodata Infosystems
Berkeley
CA
USA

(2 row(s) affected)

The display shows two rows with 14 columns each. Because of the length of the rows, each takes up multiple horizontal lines in the output.

The select list (and the results) of a join need not include columns from both of the tables being joined. For example, to find the names of the authors who live in the same city as one of the publishers, your select list need not include any columns from publishers:

SELECT au_lname, au_fname
FROM authors, publishers
WHERE authors.city = publishers.city