Using a non-correlated subquery in the WHERE clause

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

Using 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 IN operator allows you to use a subquery that returns zero or more rows.

You can execute the following query using a non-correlated subquery with IN:

USE lahmansbaseballdb;
SELECT playerid, yearid, g as GamesBatted 
FROM batting
WHERE playerid IN (SELECT playerid FROM people WHERE birthcity = 'Boston');

The preceding query returns the results shown in the following screenshot:

The preceding query will return the playerid, yearid, and GamesBatted columns when playerid has birthcity as Boston in the people table. This is because the WHERE clause has a non-correlated subquery that queries the people table for all playerid that have birthcity equal to Boston, and then queries the batting table to get the list of playerid with the corresponding yearid and GamesBatted column.

Using 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 NULL. The NOT IN operator allows you to use a subquery that returns zero or more rows.

You can also use the same non-correlated subquery, but with NOT IN in the WHERE clause instead, as shown in the following query:

USE lahmansbaseballdb;
SELECT playerid, yearid, g as GamesBatted
FROM batting
WHERE playerid NOT IN (SELECT playerid FROM people WHERE birthcity = 'Boston');

The previous query returns the results shown in the following screenshot:

The previous query will return the playerid, yearid, and GamesBatted columns when playerid doesn't have birthcity as Boston in the people table. This is because the WHERE clause has a non-correlated subquery that uses NOT IN in the query of the people table for all playerid that have birthcity equal to Boston, and then queries the batting table to get the list of playerid with the corresponding yearid and GamesBattedcolumns.

It's important to remember that if your non-correlated subquery returns any NULL values when you use IN or NOT IN, then the outer query will return no results.

Using >= (greater than or equal to): This will return results in the outer query where the results are greater than or equal to the inner query result. This can only be used if the inner query returns one value. The >= operator allows you to use a subquery that returns zero or more rows.

You can also use comparison operators such as >= (greater than or equal to) with a non-correlated subquery in the WHERE clause, as shown in the following query:

USE lahmansbaseballdb; 
SELECT playerid, yearid, salary
FROM salaries
WHERE salary >= 
 (SELECT AVG(salary)
 FROM salaries
 WHERE teamid = 'DET'
 GROUP BY teamid)
ORDER BY playerid, yearid;

The preceding query returns the results shown in the following screenshot:

The previous query will return the playerid, yearid, and salary columns if the salary of the player is greater than or equal to the average salary on the DET team.

It's important to remember that when using comparison operations such as =, >, <, >=, <=, or <>, your subquery can only return one row or you will receive an error: Error Code: 1242. Subquery returns more than 1 row.

Using ANY: This will return results in the outer query where the results of the outer query satisfy any of the results of the inner query. The ANY operator allows you to use a subquery that returns zero or more rows.

You can also use comparison operators such as >= (greater than or equal to) with ANY and a non-correlated subquery in the WHERE clause, as shown in the following query:

USE lahmansbaseballdb; 
SELECT playerid, yearid, salary
FROM salaries
WHERE salary >= ANY
 (SELECT AVG(salary)
 FROM salaries
 GROUP BY teamid)
ORDER BY playerid, yearid;

The preceding query returns the results shown in the following screenshot:

The preceding query will return the playerid, yearid, and salary columns if the salary of the player is greater than or equal to ANY of the average salaries for each teamid.

Using ALL: This will return results in the outer query where the results of the outer query satisfy all of the results of the inner query. The ALL operator allows you to use a subquery that returns zero or more rows.

You can also use comparison operators such as >= (greater than or equal to) with ALL and a non-correlated subquery in the WHERE clause, as shown in the following query:

USE lahmansbaseballdb; 
SELECT playerid, yearid, salary
FROM salaries
WHERE salary >= ALL
 (SELECT AVG(salary)
 FROM salaries
 GROUP BY teamid)
ORDER BY playerid, yearid;

The preceding query returns the results shown in the following screenshot:

The previous query will return the playerid, yearid, and salary columns if the salary of the player is greater than or equal to ALL of the average salaries for each teamid.

When using ANY or ALL with comparison operations such as =, >, <, >=, <=, or <>, your subquery can return more than one row.

Additionally, you can use any of the operators listed at the beginning of this section by following the rules for each of the operators.

Table of Contents for Using a non-correlated subquery in the WHERE clause

Create new playlist

Sign In

Sign Up

Table of Contents for
Using a non-correlated subquery in the WHERE clause