I was briefly stuck on Task 4 in this exercise (VR Startup Company) when I couldn’t get any results, although I knew that three project IDs (8, 9, 10) were not chosen by any employee. Then I looked at the hint, and my code was missing one critical line: the WHERE clause in the subquery (i.e. WHERE current_project IS NOT NULL).
.
To experiment, I changed the NOT IN to IN to get the projects that WERE chosen by an employee, and I retrieved the correct answers, namely project IDs 1-7, inclusive, and WITHOUT the WHERE clause in the subquery.
This code block returns the 7 project names that were chosen by at least one employee.
SELECT project_name
FROM projects
WHERE project_id IN (
SELECT current_project
FROM employees
)
But the weird part (weird to me, at least) is that, when I change the IN to NOT IN, I get no results instead of getting the complement (i.e. the 3 other project names that aren’t selected by at least 1 employee). Then I looked at the hint, added the WHERE clause in the subquery, and I was ok.
The values in the current_project column are therefore 1-7, and NULL. Project IDs 8, 9, and 10 aren’t in that list of values, but the existence of NULL apparently makes the statement fail.
Has anyone else experienced that, and do you know why that happens?
Task 4: What are the names of projects that were not chosen by any employees?
You want to use the condition, WHERE current_project IS NOT NULL in the subquery to only select the projects that employees are working on. The outer query uses NOT IN to identify the projects that no one is working on. The outer query works in conjunction with the inner query here.
The way you had it written–w/o the WHERE clause, and with IN on the outer query-- doesn’t isolate the projects that no one chose. It selects all the projects that employees are working on and checks the project_name where the project_id is IN the results of the projects employees are working on.
I hope that makes sense(?) I think I’ve confused myself. 
1 Like
Hi, thank you for the reply. Regarding my using the IN (instead of the NOT IN), I looked at that only after my NOT IN didn’t work (i.e. b4 I added the WHERE clause in the subquery).
As an experiment, I wanted to see if the IN captured the projects that at least 1 employee had chosen, even without the WHERE clause in the subquery. As I discovered, when I used IN, the subquery (w/o the WHERE clause) captured project ids 1,2,3,4,5,6, and 7, which is correct, as they are the only non-null project ids in the current_project column.
This is where my befuddlement kicked in. Even though the current_project column contains NULL values, my IN statement still correctly captured project ids 1-7 as the projects that at least 1 employee had chosen. Given that the full set of project ids numbered 1-10 inclusive, I had presumed that a NOT IN would capture the complement of project ids 1-7, namely 8, 9, and 10, but the NOT IN returned no results.
I now understand that both the IN and NOT IN will function properly if I have the WHERE clause in the subquery, but what I didn’t - and actually still don’t - understand is why the IN returns correct results without the WHERE clause, but the NOT IN doesn’t, even though the current_project column contains NULL values in both cases.
This is one of those behaviors I realize that I just need to accept as fact, even if it doesn’t seem logical to me.
Thank you again!
The subquery is executed first, then the outer query is executed.
this:
SELECT project_name, project_id
FROM projects
WHERE project_id IN
(SELECT current_project
FROM employees
WHERE current_project IS NOT NULL
);
returns this:
project_name project_id
AlienInvasion 1
RocketRush 2
ZombieStorm 3
BravoBoxing 4
ExtremeJets 5
MMA2K 6
FistsOfFury 7
And,
SELECT project_name, project_id
FROM projects
WHERE project_id NOT IN
(SELECT current_project
FROM employees
WHERE current_project IS NOT NULL
);
project_name project_id
CycleScenes 8
CarnivalCoasters 9
SparkPoint 10
The part in the subquery that has the condition (the WHERE current_project IS NOT NULL will result in all projects that have a value in that row (see below). That needs to be there for IN and NOT IN to work, period.
DId you try running the subquery on its own to see the results?
If you just run this query:
SELECT current_project
FROM employees;
You get a list of numbers including 0.
When you run this:
SELECT current_project
FROM employees
WHERE current_project IS NOT NULL
You get:
current_project
2
2
2
3
6
5
4
1
7
7
7
7
5
7
Your outer query will not work on the first example b/c it doesn’t know what numbers are not in that returned list.
It also might help to read up more on how these conditionals work in SQL. (b/c the current CC lessons might be insufficient).
1 Like
Hi, thank you for your reply, and I appreciate the time you spend in explaining this. This statement, at the end of your post, actually put it in perspective for me.
Your outer query will not work on the first example b/c it doesn’t know what numbers are not in that returned list.
However, you said that WHERE current_project IS NOT NULL is needed for IN and NOT IN to work.
This is true for the NOT IN but it wasn’t true for the IN (at least, for me), which was the source of my confusion. I didn’t understand why WHERE current_project IS NOT NULL is needed for NOT IN, but isn’t needed for IN.
You need the WHERE condition in the subquery and it will work with both IN and NOT IN in the outer query. Both worked for me.
You have to first look at what the inner query returns–which is why it’s good to just run it on its own (above).
When you don’t have the WHERE in the subquery, what are the results? The list of projects above–2, 2-, 3, 6, 5…
When the outer query is run with IN, it is based off of the inner query (w/o WHERE), so it returns projects 1-7 b/c those numbers are IN the results of the inner query. It won’t work with NOT IN b/c it doesn’t know what projects aren’t in the results from the inner query.
This,
SELECT project_name, project_id
FROM projects
WHERE project_id NOT IN
(SELECT current_project
FROM employees
);
reuturns nothing…b/c it cannot discern what rows aren’t in the results of the inner query.
1 Like
Thank you! Thank you! (i was going to say that once, but I needed 20 chars 
1 Like