Today I want to discuss one thing about IN clause in SQL Server.
SELECT * from
tblcustomer
WHERE
(ID, Name) in (
select CustomerID, CustomerName from tblCustomerOrders
)
In the above statement, we wanted to list the details of customers who have submitted some orders. When you run this statement in SQL Server (with the tables tblCustomer and tblCustomerOrders already created), an error is generated about this. The real reason is that SQL Server does not support more than one parameters in the IN clause.
Subscribe to:
Post Comments (Atom)
2 comments:
I agree! I've though the same thing for ages.
Limiting the IN clause to only one field prevents it from being used effectively for tables with composite keys.
You should also be able to say:
SELECT * FROM tblCustomers
WHERE (ID, Name) In ((1,'Smith'),
(53, 'Peterson'));
which is just a natural extension of the same principle.
I will whoever write SQL specs would get onto this.
I agree. This is especially a problem when using compound indexes. I have only been able to get around it by concatenating the IDs together on both sides.
Post a Comment