Hi,
Is there a reason that the following query doesn't error?
select * from sys.objects
where parent_object_id not in
(select parent_object_id from sys.columns)
The column parent_object_id doesn't exist in the sys.columns table, yet the query runs without an error.
If it's a "not in" query, then an empty resultset is returned, whereas if it's an "in" query then you get all results from the outer table.... but why?
If you run the code within the sub query on its own, it obviously fails as the column doesn't exist.
Thanks
Steve
parent_object_id is in sys.objects in the main body of the query. And subqueries inherently reference
You need to always specify the table/alias name for all columns in subqueries. By it's nature, a subquery references the main query, so if the query engine doesn't find a match in the subquery, it looks up to the main query.
In fact, you should really qualify all column. names to avoid ambiguity (and the extra time, however small, for SQL Server to have to guess).
It's kind of like the database engine guessing you want dbo if we don't specify schema for a table (don't do that either).
February 23, 2022 at 5:26 pm
A lower-level query (llq) can automatically references all columns from a higher level query (hlq). This is intentional and is not an error.
For example:
SELECT ...
FROM dbo.hlq
WHERE EXISTS ( SELECT 1 FROM dbo.llq WHERE llq.col1 = hlq.col1)
As always, SQL allows you to leave off the table/alias name if the column name is unique. SQL does that for standard joins too, of course.
In summary, this is perfectly normal behavior for SQL Server (and nearly all relational dbms's that I know of).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 23, 2022 at 5:37 pm
Thanks very much both.
I'm amazed I've not seen this before
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply