Possible mistake in the description related to EXCEPT clause?

Q&A DQ Courses
,
#1

Guided Project Step - https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/6/albums-vs-individual-tracks

In this Step, we have been told the following -

image
image852×250 8.66 KB

Here, we get a null value even though the two tables are not identical. That’s because all of the values for letter in test_table_2 are also in test_table_1 , even if test_table_1 has an extra value.

It says we get a null value from the above. Then we have -

image
image1635×948 27.7 KB

Equates to False as both are not null

But that’s incorrect. The second part of the above image (after the AND) is Null as per the previous image.

In the end, that AND still results in a False. But the explanation seems wrong.

Am I overlooking something here?

#2

hi @the_doctor

I guess English is a trap for all of us!

Here “both” is the keyword and not “not”. The author is not intending to say both of the comparisons result in True for IS NULL!
It’s more like one of them fails the condition and the other passes. So “not both of them results in IS NULL = True”.

Both you and the author are saying the same thing with different words.

I used both (4 +1) times to answer this! :rofl:

hope this helps. If not, it’s not my day today :worried: !

1 Like
#3

Then that would either be incorrect, or just poorly phrased by the author, right?

Failing the condition implies a False. Failing a condition does not imply that it is Not Null.

SELECT letters FROM test_table_2
EXCEPT
SELECT letters FROM test_table_1

The above returns a Null value as per the author. So, when they say both are not null, then that is incorrect.

(
SELECT letters FROM test_table_2
EXCEPT
SELECT letters FROM test_table_1
) IS NULL

The above would return a True.

The statement,

Equates to False as both are not null

is still not a valid explanation for the AND to result in a False.

It’s just not well phrased as per me. Should preferably be corrected/clarified.

#4

hi @the_doctor

I corrected a part of the earlier post. Also, the condition passing and failing was my own response nothing to do with the author.

I feel like you want a detailed statement here instead of the shortcut gist the author used.

@Bruno over to you.

#5

You’re not overlooking anything with regards to what you’re asking.

The given condition equates to false because one of the components is false, namely the first one. It is not true that both are not null. Specifically, the query below does return null.

SELECT letters FROM test_table_2
EXCEPT
SELECT letters FROM test_table_1

You’re not overlooking anything with regards to what you’re asking.

One thing I will say is that the given query, as it is, isn’t legal code. Something like the code below returns the error near "(": syntax error: (.

(
SELECT letters FROM test_table_1
EXCEPT
SELECT letters FROM test_table_2
) IS NULL;

You can experiment with the SQLite database file provided below.
test_database.db (16 KB)

1 Like
#6

I was getting confused because I kept reading the statement as -

Equates to False as both are not null

The focus of the statement is actually -

Equates to False as both are not null

Or an alternative way that it become clearer to me was when I read it as -

Equates to False as both aren’t null

(if the above two are not the best way to represent this, then just ignore them. I have still understood why I was mistaken)

@Rucha clarified this previously as well. Took me time to grasp it.

Thanks to you both!

1 Like
#7

I actually suggested a different fix:

Equates to False as not both are null

Thank you for your feedback, by the way. It’s always appreciated.

Edit: This has been fixed.

2 Likes
#8

Thanks so much @Bruno

This is super fast @the_doctor.

I took almost 2 weeks to understand one instruction from the probability missions :rofl: and bothered Bruno twice & both times confused him and self further :woman_facepalming:

#9

Why the error occurs? I have got the same error and dont understand.
This is the link to my question Near "(": syntax error when trying to check IS NULL. pls help!.

Can you help me?

#10

The answer to your question has already been provided, here and in your original post -

It’s not a valid query in SQL ( or SQLite). You need to use it inside a CASE statement.

2 Likes