The Problem with IN

In one of my old notes, I wrote how JOIN, IN and EXISTS are all affected by NULL values in the columns used to join tables, or to check for existence of relationship (parent-child) between tables.

Here are some queries that use IN that will not give any syntax error but could give unexpected results.

Consider this sample table:

select * from Authors
select * from books

AuthorID    AuthorName                     AuthorCity
----------- ------------------------------ ---------------
1           Jhon                           San Francisco
2           Paul                           Los Angeles
3           George                         San Diego
4           Ringo                          Oakland

aid         BookID      BookTitle                      BookPrice
----------- ----------- ------------------------------ ---------------------
1           1           Yesterday                      5.00
1           2           In My Life                     7.50
1           3           Hey Junde                      4.45
2           4           Fool On the Hill               NULL
2           6           If I Fell                      7.80
4           7           Let It Be                      NULL
4           8           Till There Was You             0.00
2           9           Yellow Submarine               34.65
1           10          I Should Have Known Better     65.33

Now, I executed this query:

select *
from authors
where  authorid in
   (select Authorid from Books 
        where BookTitle = 'Yesterday')

select *
from authors
where authorid in
   (select aid from Books
      where BookTitle = 'Yesterday')

Here are the result sets:

AuthorID    AuthorName                     AuthorCity
----------- ------------------------------ ---------------
1           Jhon                           San Francisco
2           Paul                           Los Angeles
3           George                         San Diego
4           Ringo                          Oakland

AuthorID    AuthorName                     AuthorCity
----------- ------------------------------ ---------------
1           Jhon                           San Francisco

Just like what I saw with EXISTS in my old notes, the parser did not return an error on the first query, but returned an unexpected result instead. Separately, the subquery will return an error.

select Authorid from Books
where BookTitle = 'Yesterday'

Msg 207, Level 16, State 1, Line 1
Invalid column name 'Authorid'.

Just like EXISTS, the first query did not return an error because even though AuthorID is not in Books table, because it exists in Authors table.

Unlike EXISTS, the relationship was established without the need to relate the relationship keys. To ensure accurate results, use table alias.

select *
from authors a
where  authorid in
(select b.Authorid from Books b
    where b.BookTitle = 'Yesterday')

Msg 207, Level 16, State 1, Line 4
Invalid column name 'Authorid'.

select *
from authors a
where authorid in
(select b.aid from Books b
  where b.BookTitle = 'Yesterday')

AuthorID    AuthorName                     AuthorCity
----------- ------------------------------ ---------------
1           Jhon                           San Francisco

~~CK