Prev: SQL optimisation, stop the sub query as soon as an error is found
Next: CREATE AGGREGATE failed because type 'Concatenate' does not conform toUDAGG specification due to method 'Accumulate'.
From: Peter on 12 Nov 2009 22:35
A continuing annoyance is that I can do stuff like this in SQL Server:

SELECT * FROM X WHERE A,B IN (SELECT A,B FROM Y)

This is getting more complex if any of A or B can be NULL. Is there an
alternative in SQLServer 2005+ to implement such predicates?

Pete


From: sloan on 12 Nov 2009 22:49

Select * from dbo.Employee e where exists ( select null from
dbo.SomeOtherTable sot where sot.StateID = e.StateID and sot.EmployeeID =
e.EmployeeID )

You can start there and then experiment.

Throw in a " or e.StateID IS NULL "... or something like that.

You should post some DDL , some INSERTS and then desired results
..................


"Peter" <peteATkapiti.co.nz> wrote in message
news:eJSgqJBZKHA.1336(a)TK2MSFTNGP06.phx.gbl...
>A continuing annoyance is that I can do stuff like this in SQL Server:
>
> SELECT * FROM X WHERE A,B IN (SELECT A,B FROM Y)
>
> This is getting more complex if any of A or B can be NULL. Is there an
> alternative in SQLServer 2005+ to implement such predicates?
>
> Pete
>


From: --CELKO-- on 13 Nov 2009 12:17
You have discovered Standard SQL syntax, but missed the row
constructor notation:

SELECT * FROM X WHERE (a,b) IN (SELECT a, b FROM Y);

SQL Server is behind other products. You wind up using

SELECT *
FROM X
WHERE EXISTS
(SELECT *
FROM Y
WHERE X.a = Y.a
AND X.b = Y.b);
From: Peter on 13 Nov 2009 16:13
Yes you're right, I (accidentially) did missed the row constructor syntax in
my original post.

Still, this "Standard SQL Syntax" works fine in Oracle and MySQL but NOT SQL
Server 2005. The SELECT in the following SQL will give you a syntax error.

CREATE TABLE A (x int, y int )
CREATE TABLE B (x int, y int )
INSERT INTO A (x,y) VALUES (1,1)
INSERT INTO A (x,y) VALUES (1,3)
INSERT INTO A (x,y) VALUES (2,1)
INSERT INTO A (x,y) VALUES (2,3)
INSERT INTO B (x,y) VALUES (1,1)
INSERT INTO B (x,y) VALUES (1,2)
INSERT INTO B (x,y) VALUES (1,3)
INSERT INTO B (x,y) VALUES (2,1)
INSERT INTO B (x,y) VALUES (2,2)
INSERT INTO B (x,y) VALUES (2,3)
SELECT * FROM B WHERE (x,y) IN (SELECT x,y FROM A)
DROP TABLE B
DROP TABLE A


"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:a96e328b-ed78-4650-8f61-f246e5624e45(a)o10g2000yqa.googlegroups.com...
> You have discovered Standard SQL syntax, but missed the row
> constructor notation:
>
> SELECT * FROM X WHERE (a,b) IN (SELECT a, b FROM Y);
>
> SQL Server is behind other products. You wind up using
>
> SELECT *
> FROM X
> WHERE EXISTS
> (SELECT *
> FROM Y
> WHERE X.a = Y.a
> AND X.b = Y.b);


From: Plamen Ratchev on 13 Nov 2009 16:57
Yes, vector expressions in predicates are still not supported in SQL Server. The current method using the EXISTS
predicate can be very verbose especially when NULLs are involved. Hope we see something in the next versions.

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: SQL optimisation, stop the sub query as soon as an error is found
Next: CREATE AGGREGATE failed because type 'Concatenate' does not conform toUDAGG specification due to method 'Accumulate'.