How Joins are Structured

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, SAP ASE 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.