| Author |
 Topic  |
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
 Posted - 10/15/2003 : 19:38:38
|
An excellent little tid-bit (I ignored the speeling eras).
I EXIST therefore I am. 
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
Edited by - rrb on 10/15/2003 19:39:23 |
 |
|
|
jbutler003
Starting Member
USA
1 Posts |
Posted - 10/16/2003 : 09:32:54
|
The other great place to use EXISTS is in a subquery. I have run across a lot of queries that do something like:
SELECT t.myColumns
FROM myTable t
WHERE t.myCode IN ( SELECT l.allCodes FROM l.lookupTable )
Like your example, this works well with small amounts of data, but falls apart as the lookupTable grows. Much better is:
SELECT t.myColumns
FROM myTable t
WHERE EXISTS( SELECT 1 FROM lookupTable l WHERE l.allCodes = t.myCode ) |
|
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 10/16/2003 : 17:58:56
|
I'll bet a beer that this is faster:
SELEC T.mycolumns
FROM MyTable T
INNER JOIN LookupTable L ON L.allCodes = T.MyCode
|
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 10/16/2003 : 19:39:38
|
| I'll bet you two beers they generate the same execution plan. |
|
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 10/16/2003 : 20:44:51
|
I'll take the bet to keep this lively, but I suspect you may be right !
|
|
|
|
TallCowboy0614
Starting Member
USA
17 Posts |
Posted - 02/10/2004 : 20:45:15
|
But what about the instance where you wanna select a column from the row you are testing for, such as:
I'm trying to be slick, but so far am just all wet *snicker*
I've got a table with rows that may or may not exist, and am trying to retrieve a column if an associate row DOES exist.
For argument's sake, my PIndex table looks like:
PID int
CreateDate smalldatetime
CloseID float
Here is my select logic that I thought would save an extra select to load my local variable if the row is actually in the table...
DECLARE @CloseID float
IF NOT EXISTS (SELECT @CloseID = CloseID
FROM PIndex
WHERE ((PID = '14') and
(CreateDate = '2004-02-06')))
SET @CloseID = 100
The SET afterwards is just to initialize the variable if it can't be had from an existing row in the table.
The trouble is, that it fails to compile with the following error:
>>>>> Line 3: Incorrect syntax near '='.
Any insights? My goal is to use a single select to load the value into my local variable if the associated row exists, or to set my local variable to 100 if it doesn't.
Thanks!
Paul
|
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 02/10/2004 : 20:58:35
|
OK, I may be missing something, but why not just do...DECLARE @CloseID float
IF EXISTS (select CloseID FROM PIndex WHERE ((PID = '14') and (CreateDate = '2004-02-06')))
select @closeID = CloseID FROM PIndex WHERE ((PID = '14') and (CreateDate = '2004-02-06'))
else
SET @CloseID = 100
?
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
TallCowboy0614
Starting Member
USA
17 Posts |
Posted - 02/10/2004 : 20:59:59
|
GREAT IDEA!!! I tried it and got the error:
Incorrect syntax near the keyword 'FROM'.
But I betcha it's just a synax error...I have actually never used the CASE function before...so thanks for opening my eyes to that. I'll play around a bit more and see if I can fix it.
sounds like it's tailor-made for what I need! :) |
|
|
|
TallCowboy0614
Starting Member
USA
17 Posts |
Posted - 02/10/2004 : 21:01:17
|
rrb - I am just trying to avoid the double select, if possible...
perhaps a misinformed attempt at saving some resources and time...but I'm TRYIN' *LOL* |
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 02/10/2004 : 21:02:33
|
CASE function? where did that come from?
PS to be really strictly correct, your date comparisons assume your date is stored as a string. Is this what you want?
Anyhow, ehorns variation is the same, just move the else out in front
DARN that ehorn guy. More slipery than a wet pineapple
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
Edited by - rrb on 02/10/2004 21:10:33 |
|
|
|
ehorn
Flowing Fount of Yak Knowledge
USA
1629 Posts |
Posted - 02/10/2004 : 21:04:10
|
Or initialize @CloseID to 100 and then check for existance. If the conditions are met @CloseID will be updated otherwise it will remain 100 and this only requires 1 select.
SET @CloseID = 100
SELECT @CloseID = CloseID
FROM PIndex
WHERE PID = '14'
and CreateDate = '2004-02-06'
SELECT @CloseID
PS. Rob, I originally had a case statement posted but replaced it after re-reading the post, Sorry for the confusion. |
Edited by - ehorn on 02/10/2004 21:06:11 |
|
|
|
TallCowboy0614
Starting Member
USA
17 Posts |
Posted - 02/10/2004 : 21:05:48
|
ehorn - yeah, good idea that too...I thought about that earlier today, but since my logic is in the middle of a transaction, won't the failure if the row doesn't exist cause me to rollback?
rrb = sorry about that...the date in my app is actually a variable, just tried to make the example simpler *LOL* *blush* |
|
|
|
ehorn
Flowing Fount of Yak Knowledge
USA
1629 Posts |
Posted - 02/10/2004 : 21:12:27
|
TallCowboy0614, This will not cause a rollback as all you are doing is assiging a variable, Try this example to see what is happening here:
create table #test (n int)
insert into #test select 1
declare @n int
set @n = 100
select @n = n
from #test
where n = 2
select @n --still 100
drop table #test |
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 02/10/2004 : 21:18:38
|
not unless you issue a
rollback tran
Which you could do if you want to, like this:
begin tran t1
DECLARE @CloseID float
set @closeID = 100
SELECT @CloseID = CloseID FROM PIndex WHERE PID = '14' and CreateDate = '2004-02-06'
if @@ROWCOUNT = 0
rollback tran t1
else
begin
select @closeID
commit tran t1
end Otherwise, just stick with the ehorn method. He may be slippery, but I like him...
[Later]Hey: wouldn't it be grand if my 999th post was a load of crap which didn't work?
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
Edited by - rrb on 02/10/2004 21:25:12 |
|
|
|
TallCowboy0614
Starting Member
USA
17 Posts |
Posted - 02/11/2004 : 11:20:08
|
Thanks guys - I continue to learn, which is a good thing, for if we stop learning, we perish... (wow, I think I need a cigar and a smoking jacket before I say stuff like that... ;) )
Anyway, I ended up going the CASE route...just modified a tad...
DECLARE @CloseID INT
SET @CloseID = 100
SELECT @CloseID = CASE
WHEN CloseID IS NULL THEN 100
ELSE CloseID
END
FROM PIndex
WHERE ((PID = 14) and (CreateDate = '2004-02-06'))
As it turns out, my primary concern is that a ROW doesn't exist with the CloseID value I'm after, as the row is populated originally with a value in this column (and so, should never be NULL.
And without the initial setting of the @CloseID column to 100, if the row does not exist, it won't be set to 100 by the select/case statement (lesson learned here is a missing row doesn't set the test value to NULL, apparently) - - In any case, the select WITHOUT the pre-initialization sets the value to 100 ONLY if the row exists, but the column is NULL in the table.
Thanks again SO much for your time an effort, guys...I know time is a commodity, and I appreciate you spent some helping me. I LOVE the way the internet allows us to amass a few good minds where before we'd only have a few guys in the cubes around us as resources for stuff like this.
~Paul~
aka "Paul"
Non est ei similis.
SCC Member # 240
CCOA Member # 8179
Curbster Member # 0.947300123578
(Without [URL=http://www.thecurb.org]The Curb[/URL], a road is just a meandering slab of asphalt.)
"He's not the Messiah. He's a very naughty boy!" - Brian's mum |
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 02/11/2004 : 17:19:08
|
Ah, yes, now I see. You wanted to also set the value to 100 if the actual value was null. Sorry, I didn't pick that up before. But you could replace the case with SELECT @CloseID = IsNull(CloseID,100) if you wanted.
Glad to hear you got it going.
Cheers
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
ian.stone
Starting Member
United Kingdom
0 Posts |
Posted - 12/09/2004 : 08:20:21
|
| I have had to remove IF EXISTS and replace with SELECT COUNT because of serious performance issues. Typicialy I found IF EXISTS taking 30+seconds whereas SELECT COUNT took less than 1 second. On the face of it it looks daft but it is true. IF EXISTS looks like a poor mans version of R:Base's WHERE LIMIT=1 which incedently works. |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 12/09/2004 : 09:25:45
|
Can you give an example of a situation in which this occured? show us what you had (keep it short -- only relevent parts of the SELECT) and what you changed it to, along with the table(s) involved and all relevent indexes.
I'd be surprised that COUNT would be faster, since by definition COUNT needs to return and count up ALL rows, whereas EXISTS() can just stop as soon as it finds just 1 row.
- Jeff |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 12/09/2004 : 09:37:51
|
quote:
I'd be surprised that COUNT would be faster
I'd be surprised that he's using SQL Server. If possible, please also post the execution plan you get from both methods. |
|
|
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 01/21/2005 : 01:17:17
|
The best use for EXISTS in SQL Server is in updateable view definitions where enforcing the CHECK OPTION cannot be done with a JOIN condition.
DavidM
"Always pre-heat the oven" |
|
|
|
Topic |
|
Article: Using EXISTS
