Why SQL not able to show compile time error of inner query which has invalid column

Lokesh Kumar 0 Reputation points

Consider below Query where we have two tables tbl_Product and tbl_Order, tbl_Product has column ProductId but tbl_Order doesn't have column ProductId. Still below query runs and it doesn't even give any compilation error.

Select ProductId, ProductName, * from dbo.tbl_Product where ProductId IN (

Select ProductId from dbo.tbl_Order where OrderId = XXXX


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,342 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 114.7K Reputation points

    It is not an error because “The query that follows IN is correlated. That is, it can refer to tables outside the subquery”:

    The subquery takes the value of ProductId from tbl_Product, because it was not found in tbl_Order.

    1 person found this answer helpful.

  2. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor

    Hi @Lokesh Kumar

    Similar questions have been raised several times before.


    The answer in this thread is very detailed, you can refer to it.

    Best regards,

    Percy Tang

    0 comments No comments

  3. Olaf Helper 43,166 Reputation points

    it doesn't even give any compilation error.

    Why shoul it, it's a valid SQL statement and the column ProductId exists in the out, referencable table tbl_Product.

    So no bug, only a logical failure on your side and that's the reason, why we alway should use full-qualified object names.

    0 comments No comments