Referencing Outer Query Tables in Inner Query

WRONG:

SELECT * FROM Table t1 INNER JOIN  ( SELECT TOP 1 t1.PrimaryKeyId Table t2 ON where t2.CustomerId = t1.CustomerId ORDER BY CreatedDate DESC)

When you run the above query, you would get an error – “Multi-Part Identifier Could Not Be Found”.

CORRECT:

SELECT * FROM Table t1 where t1.PrimaryKeyId = (SELECT TOP 1 PrimaryKeyId FROM  Table t2 WHERE t2.CustomerId = t1.CustomerId ORDER BY CreatedDate DESC)

This sub query runs for each of the PrimaryKeyIds from the Outer Query and get the TOP 1 row from the set of rows with same CustomerIDs.

Confusion with NULL values in SQL

Today I was working on a SQL query, and it took me a while before I figured out an issue.

A query goes something like this –  SELECT * FROM Table t1 where t1.UserId NOT IN ( SELECT UserId FROM Table t2 WHERE t2.Status = 1) WHERE Status = 2

Lets assume Status 1 = Active and Status 2 = Terminated and UserId can be NULL. There are few records in Table with UserIds Null for Status = 1. When i ran the query I was expecting few records to output but for some reason there are 0 records. After looking at the query carefully, i noticed that the inner query was returning NULL values and that ends being as below. It return NULL

SELECT * FROM Table t1 where t1.UserId NOT IN ( 1, 2, NULL) — Return 0 records although there are matching records with 1, 2

NULL can be dangerous enough to mess up the whole query and result set. So better handle NULL values properly.