FAQ: Subqueries - Exists and Not Exists

This community-built FAQ covers the “Exists and Not Exists” exercise from the lesson “Subqueries”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Analyze Data with SQL

FAQs on the exercise Exists and Not Exists

There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply (reply) below.

If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!
You can also find further discussion and get answers to your questions over in #get-help.

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head to #get-help and #community:tips-and-resources. If you are wanting feedback or inspiration for a project, check out #project.

Looking for motivation to keep learning? Join our wider discussions in #community

Learn more about how to use this guide.

Found a bug? Report it online, or post in #community:Codecademy-Bug-Reporting

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

I’m not quiet get the instruction… “Write a query to find out which grade levels are represented in both band and drama.”.
I’ve looked at the hint. If subquery give us at least one record, we get all the results from main query. As a result - the answer just all grades from the first table. But the exercise is “Write a query to find out which grade levels are represented in BOTH band and drama.”
If I add new student with grade = 1000)) it will be in query result!
The answer must be “9,10,11 and 12”. Isn’t it?
We can do it without EXISTS:

SELECT band_students.grade
FROM band_students
JOIN drama_students
  ON band_students.grade = drama_students.grade 
GROUP BY 1;

With EXISTS we need to add condition:

SELECT grade
FROM band_students
WHERE EXISTS (
  SELECT * 
  FROM drama_students
  WHERE band_students.grade = drama_students.grade 
) 
GROUP BY 1;

Without the condition, we’ll get all rows from outer query again.
If I get it correctly, with inner condition we check every row from band_students table and get TRUE or FALSE in WHERE EXIST statement. If grade in first row of band_students table isn’t equal any grade in drama_students we’ll get FALSE and this grade won’t get into result.

SELECT grade 
FROM band_students
WHERE grade IN(
  SELECT grade
  FROM drama_students
)
GROUP BY 1;

Wouldn’t it be more efficient to use IN clause?

1 Like

I agree with you it’s an odd question and probably not the best example of using the ‘Exists’ clause.

You can get the same results with

SELECT distinct bs.grade
FROM band_students bs
INNER JOIN drama_students ds
ON bs.grade = ds.grade

I’m fairly certain the EXISTS example given is wrong.

It says that the correct answer is:
SELECT grade
FROM band_students
WHERE EXISTS (
SELECT grade
FROM drama_students);

The way this is written it will ALWAYS return all of the grades in band regardless of whether or not they are in drama. (In this particular example, it happens to work because all of the grades in band are also in drama.)

For example if you make a drama table that only has grade 9 and then run the same query as above, it still returns all four grades (9,10,11,12):

WITH drama_short AS (
SELECT *
FROM drama_students
WHERE grade = 9
)
SELECT grade
FROM band_students
WHERE EXISTS (
SELECT grade
FROM drama_short);

This returns 9,10,11,12 even though drama_short only contains 9.

In order to use EXISTS correctly, the subquery must have a condition. For example, this DOES work as intended, returning only 9:
WITH drama_short AS (
SELECT *
FROM drama_students
WHERE grade = 9
)
SELECT grade
FROM band_students
WHERE EXISTS (
SELECT grade
FROM drama_short
WHERE band_students.grade = drama_short.grade)

Here’s another example using first name. If you follow the same syntax suggested for the given answer for grade, but simply replace grade with first name, then the query returns ALL first names in band, regardless of whether or not they are in drama.

Get a sense of the first few names in band:
SELECT
first_name
FROM band_students
ORDER BY 1
LIMIT 5;

Get a sense of the first few names in drama:
SELECT
first_name
FROM drama_students
ORDER BY 1
LIMIT 5;

Use the exercises’ given syntax to try to get the names that are in both band and drama:
SELECT
first_name
FROM band_students
WHERE EXISTS(
SELECT first_name
FROM drama_students
)
ORDER BY 1
LIMIT 5;

And you find that this returns all names in band, regardless of whether or not they are in drama.

If you add a where clause inside the subquery then it works, as intended:
SELECT
first_name
FROM band_students
WHERE EXISTS(
SELECT first_name
FROM drama_students
WHERE band_students.first_name = drama_students.first_name
)
ORDER BY 1
LIMIT 5;

This returns only the names of those band students who are also in drama.

It’s disappointing that this lesson not been corrected in over 18 months. As many others have pointed out its wrong in a number of unhelpful ways.

Mainly in that it doesn’t make clear that if you are using the EXISTS statement then there needs to be a WHERE condition in the sub-query - literally every other on-line learning source I have looked at over the last half hour (having been thoroughly confused by this lesson) makes this clear - even W3Schools! Also the data set is unhelpful as there are no different grades between the two data-sets and so it will give students a false positive and a mis-understanding of the EXISTS clause. Come on Codecademy please fix this!