Merge pull request swcarpentry#314 from danmichaelo/ep5-sol

Ep 5: Add solution to the exercise "NULL in a Set"
tanash1983 · Jun 4, 2020 · c6c4a73 · c6c4a73
2 parents 147b416 + 590ef36
commit c6c4a73
Showing 1 changed file with 22 additions and 2 deletions.
diff --git a/_episodes/05-null.md b/_episodes/05-null.md
@@ -226,15 +226,35 @@ detail in [the next section]({{ site.github.url }}/06-agg/).
 
 > ## NULL in a Set
 >
-> What do you expect the query:
+> What do you expect the following query to produce?
 >
 > ~~~
 > SELECT * FROM Visited WHERE dated IN ('1927-02-08', NULL);
 > ~~~
 > {: .sql}
 >
-> to produce?
 > What does it actually produce?
+> > ## Solution
+> >
+> > You might expect the above query to return rows where dated is either '1927-02-08' or NULL.
+> > Instead it only returns rows where dated is '1927-02-08', the same as you would get from this
+> > simpler query:
+> >
+> > ~~~
+> > SELECT * FROM Visited WHERE dated IN ('1927-02-08');
+> > ~~~
+> > {: .sql}
+> >
+> > The reason is that the `IN` operator works with a set of *values*, but NULL is by definition
+> > not a value and is therefore simply ignored.
+> >
+> > If we wanted to actually include NULL, we would have to rewrite the query to use the IS NULL condition:
+> >
+> > ~~~
+> > SELECT * FROM Visited WHERE dated = '1927-02-08' OR dated IS NULL;
+> > ~~~
+> > {: .sql}
+> {: .solution}
 {: .challenge}
 
 > ## Pros and Cons of Sentinels