Learn SQL Database Programming

Using correlated subqueries

You can use a correlated subquery in a WHERE clause along with these operators:

• IN: This will return results in the outer query where the results are in the inner query. If the inner query returns even one NULL value, then there will be no outer query results. This is because IN can evaluate to either true, False, or NULL. The following shows some sample syntax:

SELECT column(s) FROM table aWHERE a.col IN (SELECT b.col                 FROM table b                 WHERE a.col = b.col); 

• NOT IN: This will return results in the outer query where the results are not in the inner query. If the inner query returns even one NULL value, then there will be no outer query results. This is because NOT IN can evaluate to either true, false, or