Outer Joins and Aggregate Extended Columns

An outer join and an aggregate extended column, if you use them together, and if the aggregate extended column is a column from the inner table of the outer join, cause the result set of a query to equal the result set of the outer join.

An outer join connects columns in two tables by using the SAP outer join operator, =* or *=. These symbols are Transact-SQL extension syntax. They are not ANSI SQL symbols, and “outer join” is not a keyword in Transact-SQL. This section refers only to SAP ASE syntax.

The column specified on the side of the asterisk is the outer column from the outer table, for the purposes of the outer join.

An aggregate extended column, although it uses aggregate functions (max, min), is not included in the group by clause of a query.

For example, to create an outer join for which the result contains a null-supplied row, enter:

select publishers.pub_id, titles.price
from publishers, titles
where publishers.pub_id *= titles.pub_id
and titles.price > 20.00
pub_id price

------ ----------------- ------
0736 NULL
0877 20.95
0877 21.59
1389 22.95

(4 rows affected)

Similarly, to create an outer join and aggregate column for which the result contains a null-supplied row, enter:

select publishers.pub_id, max(titles.price)
from publishers, titles
where publishers.pub_id *= titles.pub_id
and titles.price > 20.00
group by publishers.pub_id
pub_id

--------------- --------
0736 NULL
0877 21.59
1389 22.95
(3 rows affected)

To create an outer join and aggregate column with an aggregate extended column, for which the result contains a null-supplied row, enter:

select publishers.pub_id, titles.title_id,
max(titles.price)
from publishers, titles
where publishers.pub_id *= titles.pub_id
and titles.price > 20.00
group by publishers.pub_id

--------------- --------
....
(54 rows affected)