If IN is an OR .. what would be the equivalent AND

when one uses a where clause like
Select ...
where ID IN (125,234,300)
It's a shorthand OR operation.
I am wondering if there's a similar way to do an AND? Or do we need to
simply use the usual syntax:
Select ...
where ID = 125 and ID = 234 and ID = 300
Is there a better/shorthand way to achieve multiple ANDs?No, not really. IN() is not just shorthand for OR predicates; you can
have a set in the parentheses that's returned from a subquery (like
"WHERE c1 IN (SELECT Foo FROM dbo.Bar) ...") - you can't do that with OR
predicates.
Often joins can produce the same behaviour that AND predicates can. For
example, if you had a table that had all those IDs in it, you could
simple do an INNER JOIN with your main table to achieve the same
affect. For example:
SELECT * FROM MyTable AS t
INNER JOIN MyIDTable AS i ON t.ID = i.ID
But this would require creating the table if it doesn't already exist
and then populating it. However, this approach can sometimes, in
specific cases, perform better than long strings of ANDs especially if
the query optimiser can take advantage of indexes that have been defined
on the tables.
*mike hodgson*
http://sqlnerd.blogspot.com
rsphere@.gmail.com wrote:

>when one uses a where clause like
>Select ...
>where ID IN (125,234,300)
>It's a shorthand OR operation.
>I am wondering if there's a similar way to do an AND? Or do we need to
>simply use the usual syntax:
>Select ...
>where ID = 125 and ID = 234 and ID = 300
>Is there a better/shorthand way to achieve multiple ANDs?
>
>|||If the column ID is an IDENTITY or UNIQUE column, then your logic will
return nothing: It is impossible a value in this column could be equal to
125 and also equals to 234...
If your real intention is the select records with its ID column's value is
125, OR 234, OR 300, it is perfect to use IN clause.
If your intention is to NOT select anything, then you can use "WHERE ID=125
AND ID=235...", but you may make it simpe: "SELECT...FROM...WHERE 1=2"
<rsphere@.gmail.com> wrote in message
news:1141689883.663017.174190@.v46g2000cwv.googlegroups.com...
> when one uses a where clause like
> Select ...
> where ID IN (125,234,300)
> It's a shorthand OR operation.
> I am wondering if there's a similar way to do an AND? Or do we need to
> simply use the usual syntax:
> Select ...
> where ID = 125 and ID = 234 and ID = 300
> Is there a better/shorthand way to achieve multiple ANDs?
>|||thanks for the replies.
the id column is not indentity - i should have specified that to be
more clear.
heres the query:
SELECT td.TestDescriptionID as tid, (td.TestName + td.ScoreType +
td.Category + td.SubCategory) as fulltestname FROM
TestDescription td INNER JOIN ReportFact rf ON
td.TestDescriptionID = rf.TestDescriptionID WHERE (rf.DistrictID =
@.DistrictId) and rf.CampusID in (767,795,942,768) and (rf.TotCount <>
0) GROUP BY td.TestDescriptionID, td.TestName, td.ScoreType,
td.Category, td.SubCategory order by fulltestname
What I need however is a list of only the tests that all campuses
(767,795,942,768) use. Not the total list of tests that any of the
campuses use.
Would AND still be best?
I am just worried about performance. The list is coming from a
dropdownlist - and they can select any number of items in the list.|||These are equivelent:
rf.CampusID in (767,795,942,768)
(rf.CampusID = 767 OR
rf.CampusID = 795 OR
rf.CampusID = 942 OR
rf.CampusID = 768)
NOTE THE PARENTHESES!! This set of ORs has to be in parentheses so it
can then be ANDed with the other criteria.
Personally I find the IN approach to be clearer and simpler, and it
will perform at least as well as the ORs.
Roy Harvey
Beacon Falls, CT
On 6 Mar 2006 17:12:20 -0800, rsphere@.gmail.com wrote:

>thanks for the replies.
>the id column is not indentity - i should have specified that to be
>more clear.
>heres the query:
>SELECT td.TestDescriptionID as tid, (td.TestName + td.ScoreType +
>td.Category + td.SubCategory) as fulltestname FROM
>TestDescription td INNER JOIN ReportFact rf ON
>td.TestDescriptionID = rf.TestDescriptionID WHERE (rf.DistrictID =
>@.DistrictId) and rf.CampusID in (767,795,942,768) and (rf.TotCount <>
>0) GROUP BY td.TestDescriptionID, td.TestName, td.ScoreType,
>td.Category, td.SubCategory order by fulltestname
>What I need however is a list of only the tests that all campuses
>(767,795,942,768) use. Not the total list of tests that any of the
>campuses use.
>Would AND still be best?
>I am just worried about performance. The list is coming from a
>dropdownlist - and they can select any number of items in the list.|||But i need AND functionality to reduce the set to only matches in all
campuses. I love the IN statement too - but was hoping that there was
something like it with AND behavior...but I gather there is not.|||Yes. The IN (SELECT x FROM Foobar) predicate is the same as
x = ANY(SELECT x FROM Foobar)
Likewise IN (<list> ) is the same as
x = ANY(VALUES <list> )
We also have the
x = ALL (SELECT x FROM Foobar)
or
x = ALL (VALUES <list> )
It is a part of SQL that a lot of newbies do not know about.|||I guess I just don't see what your problem is. If you really must
have AND, there is yet another alternative.

>These are equivelent:
>rf.CampusID in (767,795,942,768)
>(rf.CampusID = 767 OR
> rf.CampusID = 795 OR
> rf.CampusID = 942 OR
> rf.CampusID = 768)
This is the same as either of the above, for what that is worth.
rf.CampusID <> 767 AND
rf.CampusID <> 795 AND
rf.CampusID <> 942 AND
rf.CampusID <> 768
Roy Harvey
Beacon Falls, CT
On 6 Mar 2006 17:47:51 -0800, rsphere@.gmail.com wrote:

>But i need AND functionality to reduce the set to only matches in all
>campuses. I love the IN statement too - but was hoping that there was
>something like it with AND behavior...but I gather there is not.|||i guess I am not explaining it very well.
i was simply polling to see if there was a shorthand or efficient
alternative to using
rf.CampusID= 767 AND
rf.CampusID = 795 AND
...
Since I am in a situation where a user might select up to 40 or 50
items. That's a lot of ANDs... and may not perform well.
I am not a very experienced SQL programmer so I am not sure if I am
explaining this accurately.|||On 6 Mar 2006 18:23:18 -0800, rsphere@.gmail.com wrote:

>i guess I am not explaining it very well.
>i was simply polling to see if there was a shorthand or efficient
>alternative to using
>rf.CampusID= 767 AND
>rf.CampusID = 795 AND
It can never be true that:
rf.CampusID= 767 AND
rf.CampusID = 795
It can be equal to one, or the other, or neither, but it can NOT be
equal to both, and that test would only be true if it was equal to
both.

>Since I am in a situation where a user might select up to 40 or 50
>items. That's a lot of ANDs... and may not perform well.
Use IN. That is what it is there for. It is clearer than any of the
alternatives. It is as efficient as any alternative that will produce
the same results. The optimizer will see to that, you don't have to
second guess it. Concentrate on writting clear SQL that is correct.
Until you have done that it is futile to worry about efficiency. Once
you have done that, you usually find that it is efficient - or that
you need to look at indexing.
Good luck!
Roy Harvey
Beacon Falls, CT