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

IF {ELSE IF} Construct

Hi

Since there is no IF {ELSE IF} constructs in TSQL, I assume the following will do the equivalent of ELSE IF, please verify. Thanx :)

IF condition
BEGIN
-- some TSQL
END
ELSE IF condition
BEGIN
-- some TSQL
END
ELSE
BEGIN
-- some TSQL
END

JamesPlease comment on any deviation from standard programming that this IF ELSE IF construct may introduce. I am too novice to see it.

Cheers

James|||This should work, but your code will be more readable if you can use a CASE statement instead.

blindman|||true
but isn't IF and ELSE more efficient than CASE
I assume that because what i said is true in general programming

cheers
james|||For a single criteria IF ELSE is probably more efficient, but when you start nesting IF statements I doubt there is any difference. The db engine has to make the same logical comparisons in either case.

Evaluation of a CASE statement completes as soon as a match is found, and further criteria are not considered. I'm not sure if this is true of nested IF/ELSE statements; ie, the optimizer may evaluate the entire statement. Perhaps someone else on the forum knows how the optimizer handles this scenario.

Truth is, neither of these is a very fast operation when performed against large tables. You gotta do what you gotta do.

blindman|||Originally posted by nano_electronix
Hi

Since there is no IF {ELSE IF} constructs in TSQL, I assume the following will do the equivalent of ELSE IF, please verify. Thanx :)

IF condition
BEGIN
-- some TSQL
END
ELSE IF condition
BEGIN
-- some TSQL
END
ELSE
BEGIN
-- some TSQL
END

James

I think this is the correct solution. Lets say "condition" refers to weekend day, a holiday or a week day flag and "TSQL" refers to three totaly diffrent queries. Your code would be resonable.

Now lets say "TSQL" is identical except for the treatment of a date column and all you want is the words "Holiday", "Weekend" or "Weekday" returned in your query, a CASE statment might be the better choice.

Clear as mud?|||agree :)

identity insert

Hi

I'm a SQL Server Programmer having a first touch with Oracle.

Does anyboady know the equivalent of @.@.IDENTITY in PL-SQL? This is used in SQL Server to get the value of the last inserted identity field.

for exemple:
I have a table 'user' with an identity field 'id_user'.

_______
INSERT user (name, password) VALUES ('climber','pokomoonshine')
SELECT @.@.IDENTITY
_______

Last statement is supposed to give me the value created by my INSERT statement in 'id_user', so you don't have to write SELECT MAX(id_user)...

I didn't find any topics in this forum about it yet.

Any help is welcome.oracle uses sequences instead of identity fields, and sequences are separate from tables

you would use SEQUENCE.NEXTVAL in the insert statement, and then you can use SEQUENCE.CURRVAL (in the same session) for the number that was used

here's a good example --

This example adds a new order with the next order number to the master order table. It then adds suborders with this number to the detail order table:

INSERT INTO orders (order_id, order_date, customer_id)
VALUES (orders_seq.nextval, TO_DATE(SYSDATE), 106);

INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 1, 2359);

INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 2, 3290);

INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 3, 2381);

the above example is from http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/sql_elements6.htm

rudy|||This is validating the information I got.

I learned about Oracle and saw you have to work with those functions and define your ID autoincrement into a trigger wich is a different (but interesting!) approach from SQL Server.

Thanks a lot!