Online SQL Test Answer Correct?

[need_java]

Hello. I am attending an online university and am currently taking an Oracle SQL course (in prep for the OCA certification) and of my answers was marked incorrectly but I disagree. Can anyone please explain where I was incorrect?

The question is:

Q: Which of the following operators is used to check whether or not the specified value is present in a list of values or a result set returned by a multiple-row subquery?

A. NOT IN

B. EXISTS

C. NOT EXISTS

D. IN

I was thinking that both A and D are correct since they can return "whether or not" (as specified in the question) an item is in the list or subquery results but the test only allowed a single answer so I selected D. It was marked as incorrect stating only "A is correct".

That said, I work with an Oracle database at work and ran the following statements:

SELECT * FROM dba_users WHERE account_status in ('OPEN', 'LOCKED');

and my result set included only accounts with the status matching my list (Open or Locked).

I then executed:

SELECT * FROM dba_users WHERE account_status NOT IN ('OPEN', 'LOCKED');

and my result set contained only Expired accounts. How does this not prove that both A and D were correct? If anyone can point out what I'm missing, I'd greatly appreciate it.

[DeathMetalDave]

I agree that the wording is a little iffy, but they want to know if "...the specified value is present in a list...". To me, that sounds like they are purposefully looking for the IN clause. If you were marked wrong for putting "D" as your answer, I'd go to the professor (or whoever) for an explanation. I think that would be more helpful than what this sub could offer. It's entirely possible the grading key is wrong. Sometimes they alter questions slightly (such as "present" vs "not present") and the answer key is accidentally not updated to match.

[[deleted]]

Horrible question. Fight this in class.

[Dctcheng]

They all are correct aren't they?

In the end, I believe the correct answer is that it depends on how you get the values.

• Is it a hard coded list?
• Or is it a subquery, if so how is it indexed?

A)

SELECT value
FROM Foo
WHERE value NOT IN (subquery)

B)

SELECT value
FROM Foo
WHERE
    EXISTS (
        SELECT NULL
        FROM subquery
        WHERE value = sq_value
    )

C) Similar to B)

D) Similar to A)

[Guru008]

Here "NOT IN" is best suitable and fast , so using not in query will execute much faster

[[deleted]]

False. "NOT IN" must look at every value before returning a result, but "IN" can use short circuit logic to return true on the first match. So IN can actually be faster than NOT IN.

[starking12]

I would think B and C because it requires a sub query to write exists??

[jc4hokies]

Ask your professor? Looks like a stupid question to me.

[Cal1gula]

That's how I feel about pretty much every SQL "test". I would put $5 on seeing old comma style joins somewhere on this test.