A join statement, like a select statement, 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. This example specifies the columns that contained the authors’ names from the authors table, and publishers’ names from the publishers tables:
select au_fname, au_lname, pub_name from authors, publishers
You do not have to qualify the columns au_fname, au_lname, and pub_name by a table name because there is no ambiguity about the table to which they belong. But the city column used for the join comparison does need to be qualified, both the authors and publishers tables include columns with that name:
select au_fname, au_lname, pub_name from authors, publishers where authors.city = publishers.city
Though neither of the city columns is printed in the results, Adaptive Server needs the table name to perform the comparison.
To specify that all the columns of the tables involved in the query be included in the results, use an asterisk (*) with select. For example, to include all the columns in authors and publishers in the preceding join query, the statement is:
select * from authors, publishers where authors.city = publishers.city
au_id au_lname au_fname phone address city state postalcode contract pub_id pub_name city state ----------- -------- -------- ------------ --------------------- ---------- ----- ---------- -------- ------ -------------------- ---------- ----- 238-95-7766 Carson Cheryl 415 548-7723 589 Darwin Ln. Berkeley CA 94705 1 1389 Algodata Infosystems Berkeley CA 409-56-7008 Bennet Abraham 415 658-9932 223 Bateman St Berkeley CA 94705 1 1389 Algodata Infosystems Berkeley CA (2 rows affected)
The output shows a total of 2 rows with 13 columns each. Because of the length of the rows, each takes up multiple horizontal lines. Whenever “*” is used, the columns in the results appear in the order in which they were stated in the create statement that created the table.
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 that live in the same city as one of the publishers, your query need not include any columns from publishers:
select au_lname, au_fname from authors, publishers where authors.city = publishers.city
Just as in any select statement, column names in the select list and table names in the from clause must be separated by commas.
