Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name
FROM customers
WHERE cust_credit_limit IN
(select cust_credit_limit
FROM customers
WHERE cust_city =’Singapore’);
Which statement is true regarding the above query if one of the values generated by the subquery is NULL?
A.
It produces an error.
B.
It executes but returns no rows.
C.
It generates output for NULL as well as the other values produced by the subquery.
D.
It ignores the NULL value and generates output for the other values produced by the subquery.
I think that C is bad!!!!
Why is not D correct???
Please somebody could explain why is C correct?
Yes, D is correct:
with tt as(
select 1 as cust_id, ‘Name1’ as cust_last_name, 10 as cust_credit_limit from dual union all
select 2, ‘Name2’, 11 from dual union all
select 3, ‘Name3’, null from dual
)select * from tt where cust_credit_limit in (null, 1, 2)
–no rows select, NULL ignores
D is correct! I’ll prove it.
Firstly, Using the Scheme HR to list employees where department_id is null:
select employee_id, last_name
from hr.employees
where department_id is null;
—
Output:
employee_id last_name
178 Grant
—
Secondly, executing the query
select employee_id, last_name
from hr.employees
where department_id in (select distinct department_id from hr.employees);
—
Output do not display employee_id = 178.
========
“IN” means “equals any”. But Null is not equal anything, including itself!!
There is mistake in the above query. It should be :
with tt as(
select 1 as cust_id, ‘Name1’ as cust_last_name,
10 as cust_credit_limit from dual
union all
select 2, ‘Name2’, 11 from dual
union all
select 3, ‘Name3’, null from dual )
select * from tt where cust_credit_limit in (NULL,10, 11);
The query returns 2 records: for cust_credit_limit 10 and 11.
Justyna, do you have e-mail) for speak more about the exam.
I am from Bolivia and I don’t speak english very well. I have aditional material for share 🙂
yes, I have my email. How we can exchange? I need to think….
Well, my e-mail is [email protected]
if you can you would write me
Best regards
D is correct??
It seems D is correct:
http://www.freelists.org/post/oracle-l/IN-ignore-null-values,5
When you use IN, you’re telling SQL to take a value and compare it against every value or set of values in a list using =. If any NULL values exist, a row will not be returned—even if both values are NULL.
D is correct
shut up and dance with me
puedo estar de acuerdo consentimiento con todo el artĂculo escribir-up
http://www.opinionesdating.com/meetic-opiniones/
C is correct it is not ignoring null values it using =Any wih null which will result in no rows.So C is correct.
you’re really a just right webmaster. The site loading pace is amazing. It seems that you’re doing any distinctive trick. Moreover, The contents are masterwork. you have performed a wonderful task in this topic!|
Major thankies for the blog article. Awesome.
http://www.scoop.it/t/internet-by-johnsmith3755102/p/4068945789/2016/09/11/dog-grooming-kennels-kingwood-tx