correlated subquery (idea)

In SQL, there are subqueries, and then there are correlated subqueries. Correlated subqueries are fairly complicated, and most of the query engines I've dealt with process them relatively slowly. Sometimes you need to do this sort of thing, though. I'll explain in terms of the following example.

SELECT * FROM noders outer
   WHERE 11/19/1999 IN 
   (SELECT node_date FROM
    FROM nodes inner
    WHERE outer.nodeid = inner.nodeid);

Ignore the names of the tables and fields. They are not meant to correspond to anything significant. They are just for demonstration. Here is how the query is evaluated by many query engines.

1. Start at the first row of the table in outer query. This is sometimes called the candidate row.
2. Refer to this row by the alias specified in the outer query. In this case, we called it "outer."
3. Execute the subquery. In order to do this, the nodeid from the candidate row is used in the subquery's predicate. This is sometimes called the outer reference. If a record is returned from the subquery, processing continues. Otherwise, no records return and processing starts over with the next candidate row.
4. The predicate of the outer query is checked against the result of the subquery (above). If the outer query returns true, the candidate row is returned as output. In this example, all results from the subquery with a node_date of 11/19/1999 are returned as output.
5. The next candidate row is processed until all rows in the outer table are tested.

Source:
Understanding SQL by Martin Gruber, ISBN 0895886448