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.