A scalar row subquery is a single-row subquery. In other words, it returns a single row. If the scalar subquery returns more than one row, it generates an error. The Oracle 9i version has more support of scalar subqueries than Oracle 8i.
▪ FOR EXAMPLE
SELECT city, state, (SELECT count(*) FROM student s WHERE s.zip = z.zip) as student_count FROM zipcode z WHERE state = 'CT'