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