IN keywork with > 1 column?

20 Dec 2008 7:17 PM

DeanB

Hi there,

I'm playing with SQL server 2008 on my home project, after several
years of using Oracle. Question is: is it possible to use IN with more
than one column?

select ADDRESS
from PEOPLE
where (FIRST_NAME, LAST_NAME) in
(
select FIRST_NAME, LAST_NAME
from SOME_OTHER_TABLE
)

Is this not allowed? I can get it to work on 1 column:

select ADDRESS
from PEOPLE
where (FIRST_NAME) in
(
select FIRST_NAME
from SOME_OTHER_TABLE
)

but that doesn't make sense. Oracle can do as many columns as you
like.

Thanks!

20 Dec 2008 7:38 PM

Eric Isaacs

What is your overall objective? Do you want to find the people with
the same first names and last names?

You could do it this way...

select ADDRESS
from PEOPLE
where (FIRST_NAME + LAST_NAME) in
(
select FIRST_NAME + LAST_NAME
from SOME_OTHER_TABLE
)

....assuming no NULL values in either name field.

But you would probably be better off with something like this for
efficiency, as concatenating strings isn't very efficient and won't
take advantage of existing indexes on those fields.

SELECT
    People.FirstName,
    People.LastName,
    People.Address
FROM
    PEOPLE
    INNER JOIN SOME_OTHER_TABLE S ON People.FirstName = S.FisrtName
AND People.LastName = S.LastName

-Eric Isaacs

21 Dec 2008 3:47 PM

Alex Kuznetsov

On Dec 20, 1:38 pm, Eric Isaacs <eisa***@gmail.com> wrote:

> What is your overall objective? Do you want to find the people with
> the same first names and last names?
>
> You could do it this way...
>
> select ADDRESS
> from PEOPLE
> where (FIRST_NAME + LAST_NAME) in
> (
> select FIRST_NAME + LAST_NAME
> from SOME_OTHER_TABLE
> )

And how would you distinguish Joe Yikes from Joey Ikes ;). Unles it is
a case sensitive collation and you make sure first letters are
uppercase.

21 Dec 2008 4:56 PM

DeanB

On Dec 21, 10:47 am, Alex Kuznetsov <alk***@gmail.com> wrote:
Show quoteHide quote

> On Dec 20, 1:38 pm, Eric Isaacs <eisa***@gmail.com> wrote:
>
> > What is your overall objective? Do you want to find the people with
> > the same first names and last names?
>
> > You could do it this way...
>
> > select ADDRESS
> > from PEOPLE
> > where (FIRST_NAME + LAST_NAME) in
> > (
> > select FIRST_NAME + LAST_NAME
> > from SOME_OTHER_TABLE
> > )
>
> And how would you distinguish Joe Yikes from Joey Ikes ;). Unles it is
> a case sensitive collation and you make sure first letters are
> uppercase.

In that case you would use another special character between the
names, for example '#'.

21 Dec 2008 11:31 PM

Alex Kuznetsov

On Dec 21, 10:56 am, DeanB <deanbrow***@yahoo.com> wrote:
Show quoteHide quote

> On Dec 21, 10:47 am, Alex Kuznetsov <alk***@gmail.com> wrote:
>
>
>
> > On Dec 20, 1:38 pm, Eric Isaacs <eisa***@gmail.com> wrote:
>
> > > What is your overall objective? Do you want to find the people with
> > > the same first names and last names?
>
> > > You could do it this way...
>
> > > select ADDRESS
> > > from PEOPLE
> > > where (FIRST_NAME + LAST_NAME) in
> > > (
> > > select FIRST_NAME + LAST_NAME
> > > from SOME_OTHER_TABLE
> > > )
>
> > And how would you distinguish Joe Yikes from Joey Ikes ;). Unles it is
> > a case sensitive collation and you make sure first letters are
> > uppercase.
>
> In that case you would use another special character between the
> names, for example '#'.

And you'll need a check constraint to guarantee that your special
character cannot be stored in neither column...

20 Dec 2008 7:40 PM

Plamen Ratchev

SQL Server does not support vector expressions in predicates. You would
have to use a different technique, for example EXISTS:

SELECT address
FROM People AS P
WHERE EXISTS(SELECT *
              FROM OtherTable AS O
              WHERE P.first_name = O.first_name
                AND P.last_name = O.last_name);

Note that depending on how Oracle handles NULLs this may have different
results than what you get in Oracle (that is, if Oracle considers {x,
NULL} equivalent to {x, NULL} then results will differ).

--
Plamen Ratchev
http://www.SQLStudio.com

21 Dec 2008 12:37 AM

DeanB

On Dec 20, 2:40 pm, Plamen Ratchev <Pla***@SQLStudio.com> wrote:
Show quoteHide quote

> SQL Server does not support vector expressions in predicates. You would
> have to use a different technique, for example EXISTS:
>
> SELECT address
> FROM People AS P
> WHERE EXISTS(SELECT *
> FROM OtherTable AS O
> WHERE P.first_name = O.first_name
> AND P.last_name = O.last_name);
>
> Note that depending on how Oracle handles NULLs this may have different
> results than what you get in Oracle (that is, if Oracle considers {x,
> NULL} equivalent to {x, NULL} then results will differ).
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

The only difference to me is in query readability - to me the first
example I had above is easier to read than any joins. I just couldn't
get the two-field version to work.

Thanks all