![]() ![]() And so it is with the inner table, if there happens to be a NULL value among those rows. … always returns false, because the NULL value could represent essentially anything, including the x. We expected the Semi Join to turn into an Anti Semi Join, but the plan now also contains a Nested Loop branch with a Row Count Spool – what’s that about? Turns out the Row Count Spool, along with its index seek, has to do with the NOT IN() and the fact that we’re looking at a nullable column. WHERE i NOT IN (SELECT i FROM #inner) Why so complicated? So what happens if we change the IN() to a NOT IN()? SELECT * This one equates to a really nice merge join, because the two tables have matching clustered indexes on the join column. Now, let’s look at some simple IN () queries. Remove 10 random rows, to make things a little more interesting. Create the inner table, fill it with a copy of the outer table,ĬREATE UNIQUE CLUSTERED INDEX UCIX ON #inner (i) Here’s a quick setup: - Create the outer table, give it some rows: Instead of comparing a fixed set of values, let’s look at a whole table. ![]() To prove this, look at the third comparison, where there’s no 1, but still a NULL. The second comparison is false, but not because there’s a 1 in the list, but rather because there’s a NULL. So the first comparison is true, and it’s unaffected by the fact that there’s a NULL value in the list we’re comparing with. (CASE WHEN 1 NOT IN (2, 3, 4, NULL) THEN 'True' ELSE 'False' END) (CASE WHEN 1 NOT IN (0, 1, NULL) THEN 'True' ELSE 'False' END) (CASE WHEN 1 IN (0, 1, NULL) THEN 'True' ELSE 'False' END) Now, let’s turn it around and look if we can look for a constant in a dataset that includes a NULL value: (CASE WHEN NULL NOT IN (0, 1, NULL) THEN 'True' ELSE 'False' END) (CASE WHEN NULL IN (0, 1, NULL) THEN 'True' ELSE 'False' END) (CASE WHEN NULL IN (0, 1) THEN 'True' ELSE 'False' END) So it stands to reason that this also applies to IN and NOT IN: - False: (CASE WHEN NULL=NULL THEN 'True' ELSE 'False' END) (CASE WHEN 0!=NULL THEN 'True' ELSE 'False' END) (CASE WHEN 0=NULL THEN 'True' ELSE 'False' END) This is because NULL values aren’t real values as such, but rather “unknowns”. For instance, a comparison between two NULL values, or even a comparison between a NULL value and a non-value, will always be false. Specifically, because of how NULL values are compared, they can dramatically affect how some lookup operations perform. So keep using WHERE NOT IN, just mind NULL.We need to talk about the nullable columns in your database. The example above as it stands does not demonstrate this but if we add a few more requirements we can demonstrate these benefits. SQL using subqueries is more “modular” and therefore easier to understand and reuse. This can lead to a more subtle insidious problem, which is duplicate rows. The alternative to a subquery is to place table joins in the main query. A fix for this is left as an exercise for the reader. If Applejack also leaves, it says 0 ponies have left. If Applejack is alone in town, it says 4 ponies have left. Lets say we want to know how many ponies are currently out of town: More insidiously NULL can sneak in via a subquery. If you are using external data, for example from XML or a CSV, you should filter out empty values. Practical advice and the subquery problem Like explained in the intro, id != NULL is always NULL, therefor the entire WHERE clause is always FALSE. It makes sense if you split the clause into individual comparisons:Īnd then remove the parenthesis using De Morgan’s laws: NULL still works intuitively when using WHERE IN: ![]() ![]() It behaves similar to NaN in floating point operations. If this does not lead to the desired result there are special operators to deal with NULL explicitly. You cannot use NULL in a comparison, it will always result in NULL. To refresh your mind: NULL indicates absence of a value. If you have ever written some SQL you are probably aware of the special status of NULL in SQL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |