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.
9,868 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 95,076 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 5,706 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
  3. Olaf Helper 29,196 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