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 Sybase 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 Sybase 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)
