ANSI Inner Joins

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.

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

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"