4 ms·
Be careful with point 6. If you use a subquery through an IN clause and it returns a NULL as one of the rows, nothing will return.
by chris_wot 13y ago
Be careful with point 6. If you use a subquery through an IN clause and it returns a NULL as one of the rows, nothing will return.
- hobs 13y agoI was just about to say that! Truthfully this is a good introduction but is definitely not everything you need to know about SQL.(Obviously) Handling nulls is just one of many sticklers people using SQL frequently screw up.
- lukaseder 13y agoThat's only true for NOT IN predicates (which is mentioned in the article) Example: http://sqlfiddle.com/#!4/d41d8/16785 http://sqlfiddle.com/#!4/d41d8/16785 with a(x) as ( select 1 from dual union all select 2 from dual union all select null from dual ) select 1 from dual where 1 in (select x from a)
- chris_wot 13y agoDoh! Of course. :-) The reason being is that IN uses an disjunction and NOT IN uses a conjunction. When you compare the value to NULL it returns UNKNOWN, which ultimately causes the Boolean expression to be false. I hope that redeems my reputation a little :-) Btw, great article!
- lukaseder 13y ago> which ultimately causes he Boolean expression to be false Strictly speaking, the boolean expression is also UNKNOWN ;-) I've tried to explain this here: http://blog.jooq.org/2012/01/27/sql-incompatibilities-not-in-and-null-values/ http://blog.jooq.org/2012/01/27/sql-incompatibilities-not-in... ... but to this date, I'm still not 100% sure if I correctly understood SQL NULL. A quite worrying thing is documented here: http://blog.jooq.org/2012/12/24/row-value-expressions-and-the-null-predicate/ http://blog.jooq.org/2012/12/24/row-value-expressions-and-th... It's about row value expressions and NULL predicates. The following are not the same! (A, B) IS NOT NULL NOT((A, B) IS NULL) > I hope that redeems my reputation a little :-) You're forgiven ;-) Thanks!
- chris_wot 13y agoDarn it, right again. Sigh. Those expressions can be worked out via first order predicate logic. Damned tricky, I can definitely see how that would trip up pretty much anyone at first glance. If you weren't aware of logical equivalences then you might be scratching your head for some time! Another great article, btw.