Joins that produce a result set that includes only the rows of the joining tables that meet the restriction are called inner joins. Rows that do not meet the join restriction are not included in the joined table. If you require the joined table to include all the rows from one of the tables, regardless of whether they meet the restriction, use an outer join. See “ANSI outer joins”.
Adaptive Server supports the use of both Transact-SQL inner joins and ANSI inner joins. Queries using Transact-SQL inner joins separate the tables being joined by commas and list the join comparisons and restrictions in the where clause. For example:
select au_id, titles.title_id, title, price from titleauthor, titles where titleauthor.title_id = titles.title_id and price > $15
See “How joins are structured”.
ANSI-standard inner join syntax is:
select select_list from table1 inner join table2 on join_condition
For example, the following use of inner join is equivalent to the Transact-SQL join above:
select au_id, titles.title_id, title, price from titleauthor inner join titles on titleauthor.title_id = titles.title_id and price > 15
au_id title_id title price ---------- -------- ------------------------ ----- 213-46-8915 BU1032 The Busy Executive’s Datab 19.99 409-56-7008 BU1032 The Busy Executive’s Datab 19.99 . . . 172-32-1176 PS3333 Prolonged Data Deprivation 19.99 807-91-6654 TC3218 Onions, Leeks, and Garlic: 20.95 (11 rows affected)
The two methods of writing joins, ANSI or Transact-SQL, are equivalent. For example, there is no difference between the result sets produced by the following queries:
select title_id, pub_name from titles, publishers where titles.pub_id = publishers.pub_id
and:
select title_id, pub_name from titles left join publishers on titles.pub_id = publishers.pub_id
An inner join can be part of an update or delete statement. For example, the following query multiplies the price for all the titles published in California by 1.25:
begin tran
update titles set price = price * 1.25 from titles inner join publishers on titles.pub_id = publishers.pub_id and publishers.state = "CA"
An ANSI join specifies which tables or views to join in the query. The table references specified in the ANSI join make up the joined table. For example, the join table of the following query includes the title, price, advance, and royaltyper columns:
select title, price, advance, royaltyper from titles inner join titleauthor on titles.title_id = titleauthor.title_id
title price advance royaltyper ----------- ------- ---------- ---------- The Busy... 19.99 5,000.00 40 The Busy... 19.99 5,000.00 60 . . . Sushi, A... 14.99 8,000.00 30 Sushi, A... 14.99 8,000.00 40 (25 rows affected)
If a joined table is used as a table reference in an ANSI inner join, it becomes a nested inner join. ANSI nested inner joins follow the same rules as ANSI outer joins.
A query can reference a maximum of 50 user tables (or 14 worktables) on each side of a union, including:
Base tables or views listed in the from clause
Each correlated reference to the same table (self-join)
Tables referenced in subqueries
Base tables referenced by the views or nested views
Tables being created with into
The on clause of an ANSI inner join specifies the conditions used when the tables or views are joined. Although you can use a join on any column of a table, your performance may be better if these columns are indexed. Often, you must use qualifiers (table or correlation names) to uniquely identify the columns and the tables to which they belong. For example:
from titles t left join titleauthor ta on t.title_id = ta.title_id
This on clause eliminates rows from both tables where there is no matching title_id. See “Self-joins and correlation names”.
The on clause often compares the ANSI joins tables, as in the third and fourth line of this query:
select title, price, pub_name from titles inner join publishers on titles.pub_id = publishers.pub_id and total_sales > 300
The join restriction specified in this on clause removes all rows from the join table that do not have sales greater than 300. The on clause can include an and qualifier to furthor specify search arguments, as illustrated in the fourth line of the query.
ANSI inner joins restrict the result set similarly whether the condition is placed in the on clause or the where clause (unless they are nested in an outer join). That is, these two queries produce the same result sets:
select stor_name, stor_address, ord_num, qty from salesdetail inner join stores on salesdetail.stor_id = stores.stor_id where qty > 3000
and:
select stor_name, stor_address, ord_num, qty from salesdetail inner join stores on salesdetail.stor_id = stores.stor_id and qty > 3000
A query is usually more readable if the restriction is placed in the where clause; this explicitly tells users which rows of the join table are included in the result set.
