Recent MSDN thread on this same topic
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ec532608-8672-4a28-8326-8821efbfa473/strange-behavior-with-types-table?forum=transactsql>Suppose we have 2 tables....a simple vendor master and a simple orders table
>
>
>CREATE TABLE dbo.VendorMaster (VendorPK int, VendorName varchar(100))
>CREATE TABLE dbo.OrderTable (OrderID int, VendorFK int, OrderDate Date, OrderAmount money)
>
>INSERT INTO dbo.VendorMaster VALUES (1, 'Vendor A'),
> (2, 'Vendor B')
>
>INSERT INTO dbo.OrderTable VALUES (1, 1, '1-1-2008', 100),
> (2, 2, '1-1-2009', 50)
>
>
>Suppose I want the names of those Vendors with an order in 2008. Assume the inserts above worked fine, and I have 2 vendor rows and 2 order rows. Yes, I could do this with a JOIN. But I'll do it with a subquery. I write the following:
>
>
>SELECT * FROM dbo.VendorMaster
> WHERE VendorPK IN (SELECT VendorPK FROM dbo.OrderTable WHERE YEAR(OrderDate) = 2008)
>
>
>Will I get back...(choose only one answer)
>
>A) zero rows
>B) 1 row
>C) 2 rows
>D) 4 rows
>E) an error message
>
>Again, don't copy/paste the code and run it.....that would be cheating :)
>
>Is this a trick question? Give it a try!
If it's not broken, fix it until it is.
My Blog