When and joins the clauses, evaluation stops as soon as any clause evaluates to FALSE. The row is skipped.
This query contains two and clauses, in addition to the correlated subquery:
select au_fname, au_lname, title, royaltyper
from titles t, authors a, titleauthor ta
where t.title_id = ta.title_id
and a.au_id = ta.au_id
and advance >= (select avg(advance)
from titles t2
where t2.type = t.type)
and price > $100
and au_ord = 1
Adaptive Server orders the execution steps to evaluate the subquery last, after it evaluates the conditions on price and au_ord. If a row does not meet an and condition, Adaptive Server discards the row without checking any more and conditions and begins to evaluate the next row, so the subquery is not processed unless the row meets all of the and conditions.
