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. The previous example specified 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, because there are columns of that name in both the authors and publishers tables:
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 display shows a total of 2 rows with 13 columns each. Because of the length of the rows, each takes up multiple horizontal lines in this display. Whenever “*” is used, the columns in the results are displayed 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.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
