Incorrect subquery column :

When writing a subquery, it is very easy to abstract yourself from the main query logic and concentrate on the subquery itself. This can lead to the innocent mistake of substituting a column from the subquery source table for a column with similar name from the main query.

Let's look at two very simple tables; one is a Sales table containing sales data, and the other is an auxiliary Calendar table that has all calendar dates and holidays (abbreviated here):

Sales table:

sale_date sale_amount
---------- -----------
2009-01-01 120.50
2009-01-02 115.00
2009-01-03 140.80
2009-01-04 100.50

Calendar table:

calendar_date holiday_name
------------- ----------------
2009-01-01 New Year's Day
2009-01-02 NULL
2009-01-03 NULL
2009-01-04 NULL
2009-01-05 NULL

Our task is to retrieve sales data for holiday dates only. It seems like a trivial query to write:

SELECT sale_date, sale_amount
FROM Sales AS S
WHERE sale_date IN (SELECT sale_date
FROM Calendar AS C
WHERE holiday_name IS NOT NULL);

However, you'll find that query simply returns all rows from the Sales table! A closer look at the query reveals that the culprit to be the SELECT list of the subquery. It accidentally references the sales_date column from the Sales table, instead of the calendar_date column from the Calendar table.

If that is the case, why did we not get an error? Although the outcome was not what we expected, this is still a valid SQL statement. When using a subquery, the outer query's columns are exposed to the inner query. Here, we unintentionally converted the self-contained subquery, to be executed once and the value passed to the outer query, to a correlated subquery, logically executed once for every row returned by the outer query.

As a result, the subquery evaluates to sale_date IN (sale_date) which is always true, as long as there is at least one holiday date in the Calendar table, and so our result set returns all rows from the Sales table. Of course, the fix is easy in this case; we simply use the correct date column from the Calendar table:

SELECT sale_date, sale_amount
FROM Sales AS S
WHERE sale_date IN (SELECT C.calendar_date
FROM Calendar AS C
WHERE C.holiday_name IS NOT NULL);

This illustrates another important point: it is a best practice to prefix columns in subqueries with table aliases. For example, if we had used an alias like this:

SELECT sale_date, sale_amount
FROM Sales AS S
WHERE sale_date IN (SELECT C.sale_date
FROM Calendar AS C
WHERE holiday_name IS NOT NULL);

Then this query would have resulted in an error – "Error: Invalid column name 'sale_date'".