an example of a correlated subquery

SELECT * FROM BigAndSmallNumbers BAS
WHERE BigNumber >
(SELECT AVG(BigNumber)
FROM BigAndSmallNumbers
WHERE SmallNumber = BAS.SmallNumber)

...is an example of a correlated subquery.

 
 

If our BigAndSmallNumbers table looks like this:

SmallNumber	BigNumber
2	18
3	234
4	3456
5	86746
2	77
3	543
4	3477
5	89898
2	99
3	666
4	9989
5	98734
5	99799

 
 

Then our results will be:

SmallNumber	BigNumber
2	77
2	99
3	666
3	543
4	9989
5	98734
5	99799