The outer join and the 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 Sybase 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 whose result contains a null-supplied row, enter:
select publishers.pub_id, titles.pricefrom publishers, titleswhere publishers.pub_id *= titles.pub_idand titles.price > 20.00pub_id price------ ----------------- ------0736 NULL0877 20.950877 21.591389 22.95(4 rows affected)
Similarly, to create an outer join and aggregate column whose result contains a null-supplied row, enter:
select publishers.pub_id, max(titles.price)from publishers, titleswhere publishers.pub_id *= titles.pub_idand titles.price > 20.00group by publishers.pub_idpub_id--------------- --------0736 NULL0877 21.591389 22.95(3 rows affected)
To create an outer join and aggregate column with an aggregate extended column, whose result contains a null-supplied row, enter:
select publishers.pub_id, titles.title_id,max(titles.price)from publishers, titleswhere publishers.pub_id *= titles.pub_idand titles.price > 20.00group by publishers.pub_id--------------- --------....(54 rows affected)
