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

Lokesh Kumar 0 Reputation points
2023-05-05T12:50:29.4333333+00:00

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
    2023-05-05T13:40:19.7+00:00

    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
    2023-05-08T01:43:36.3833333+00:00

    Hi @Lokesh Kumar

    Similar questions have been raised several times before.

    https://learn.microsoft.com/en-us/answers/questions/1104432/possible-bug-in-sql-server-2014-2019-with-temp-tab

    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
    2023-05-08T05:43:11.4333333+00:00

    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