Why Does SQL Server 2008 Management Studio Allow a Delete Query with an Invalid Column Reference in a Correlated Subquery to Execute Successfully?

SQL Server 2008 Management Studio syntax checking behavior

In SQL Server 2008 Management Studio, an unexpected behavior was observed when executing a query that contained an invalid column reference. The query is:

<code class="language-sql">delete from Photo where hs_id in (select hs_id from HotelSupplier where id = 142)</code>

Although "hs_id" is not a valid column name in the "HotelSupplier" table (the correct column name is "hs_key"), the query executes successfully and deletes all rows in the "Photo" table. This begs the question: Should the query fail due to syntax issues?

The answer lies in the concept of correlated subqueries. The "DELETE" statement is associated with a subquery, which means that the "hs_id" reference in the subquery resolves to the "hs_id" column in the outer query ("Photo").

This is valid behavior and allows queries to reference columns in the parent table without explicitly specifying the table name. In this case, the result is equivalent to:

<code class="language-sql">delete from Photo where Photo.hs_id in (select Photo.hs_id from HotelSupplier where id = 142)</code>

The pattern of the outer query is propagated to the subquery, even if the subquery itself does not explicitly project any columns.

Therefore, it is considered normal behavior for SQL Server to allow such "syntax-unconforming" queries to succeed and correlate unqualified column references in the subquery to the outer query.

The above is the detailed content of Why Does SQL Server 2008 Management Studio Allow a Delete Query with an Invalid Column Reference in a Correlated Subquery to Execute Successfully?. For more information, please follow other related articles on the PHP Chinese website!