Use the from clause to specify which tables and views to join. This is the clause that indicates to Adaptive Server that a join is desired. You can list the tables or views in any order. The order of tables affects the results that appear only when you use select * to specify the select list.
At most, a query can reference 50 tables and 46 worktables (such as those created by aggregate functions). The 50-table limit includes:
Tables (or views on tables) listed in the from clause
Each instance of multiple references to the same table (self-joins)
Tables referenced in subqueries
Tables being created with into
Base tables referenced by the views listed in the from clause
The following example joins columns from the titles and publishers tables, doubling the price of all books published in California:
begin tran
update titles set price = price * 2 from titles, publishers where titles.pub_id = publishers.pub_id and publishers.state = "CA"
rollback tran
See “Joining more than two tables” for information on joins involving more than two tables or views.
Table or view names can be qualified by the names of the owner and database, and can be given correlation names for convenience. For example:
select au_lname, au_fname from pubs2.blue.authors, pubs2.blue.publishers where authors.city = publishers.city
You can join views in exactly the same way as tables and use views wherever tables are used. Chapter 12, “Views: Limiting Access to Data” discusses views; this chapter uses only tables in its examples.
