Learn SQL Database Programming

Using a correlated subquery in the WHERE clause

This section will help you better understand how to use a correlated subquery in the WHERE clause:

• Using EXISTS: This will return results in the outer query where the results exist in the inner query. It evaluates to either true or false. The EXISTS operator allows you to use a subquery that returns exactly one row.

You can execute the following query using a correlated subquery with EXISTS:

USE lahmansbaseballdb; SELECT f.playerid, f.yearid, f.teamid, posFROM fielding fWHERE EXISTS(SELECT 1 FROM salaries s  WHERE salary < 200000 AND salary IS NOT NULL AND (f.playerid = s.playerid  AND f.teamid = s.teamid  AND f.yearid = s.yearid))ORDER BY f.playerid;

The preceding query returns the results ...