April 16, 2001
I do not use subqueries that often, but they are definitely a tool in my T-SQL drawer. I understand (I think) how subqueries work and can read them to determine what the purpose of the query is. There are times that a subquery will fit a situation better than any join that I can come up with.
That being said, I am not a guru or an expert on subqueries. There are better resources for very complicated and elegant subquery solutions to problems. However, I came across an issue recently where a subquery was behaving strangely and thought it would make interesting reading once I determined the problem. As an FYI, I did not solve this problem in the first half hour or so when I looked at it. I was busy and had to let it go for a couple days and when I came back to it, another good 30-60 minutes was spent determining the cause of the problem and verifying that I had solved it.
A developer recently sent me an email where he was asking what the difference was between the following two queries:
declare @this_id int set @this_id = 100 select sum(ol.price) from OrdLineDtl od, OrdLine ol where od.TypID = ( select TypID from Attr where AttrName = 'Item' ) and od.AttrVal = @this_id and od.OrdLineID = ol.OrdLineID go declare @this_id int set @this_id = 100 select sum(oi.price) from OrdLineDtl od, OrderLine ol where od.TypID = 53 and od.AttrVal = @this_id and od.OrdLineID = ol.OrdLineIDThese two queries prooduce the following result from Query Analyzer:
On the messages tab, I get:
Server: Msg 245, Level 16, State 1, Line 3 Syntax error converting the varchar value 'just for me.' to a column of data type int. (1 row(s) affected)While on the Results tab, I get:
Sum ---------- 1995.0000
The two statements are valid statements. The schema for the tables involved is as follows:
Create Table Attr ( TypID int, AttrName char(50) ) Create Table OrdLine ( OrderItemID int, ProductCode varchar( 50), Price money ) Create Table OrderLineDtl ( OrderItemID int, TypID int, AttrVal varchar( 50) )The AttrID of 53 does indeed match the AttrName of 'Item'.
Spend a few minutes trying to figure this one out. The analysis is on page 2.