Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using EXISTS

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-12 : 23:35:14
Many times you're required to write query to determine if a record exists. Typically you use this to determine whether to insert or update a records. Using the EXISTS keyword is a great way to accomplish this.

Article Link.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-10-15 : 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"
Go to Top of Page

jbutler003
Starting Member

1 Post

Posted - 2003-10-16 : 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 )
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-16 : 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
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-16 : 19:39:38
I'll bet you two beers they generate the same execution plan.
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-16 : 20:44:51
I'll take the bet to keep this lively, but I suspect you may be right !

Go to Top of Page

TallCowboy0614
Starting Member

17 Posts

Posted - 2004-02-10 : 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
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-02-10 : 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"
Go to Top of Page

TallCowboy0614
Starting Member

17 Posts

Posted - 2004-02-10 : 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! :)
Go to Top of Page

TallCowboy0614
Starting Member

17 Posts

Posted - 2004-02-10 : 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*
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-02-10 : 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"
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-10 : 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.
Go to Top of Page

TallCowboy0614
Starting Member

17 Posts

Posted - 2004-02-10 : 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*
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-10 : 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
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-02-10 : 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"
Go to Top of Page

TallCowboy0614
Starting Member

17 Posts

Posted - 2004-02-11 : 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
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-02-11 : 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"
Go to Top of Page

ian.stone
Starting Member

0 Posts

Posted - 2004-12-09 : 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.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-09 : 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
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-09 : 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.
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-01-21 : 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"
Go to Top of Page
    Next Page

- Advertisement -