Article: Using EXISTS - SQL Server Forums

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"

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 )

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

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.

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 !

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

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"

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! :)

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*

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"

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.

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*

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

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"

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

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"

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.

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

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.

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"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources