SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using EXISTS
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/12/2003 :  23:35:14  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

Australia
1479 Posts

Posted - 10/15/2003 :  19:38:38  Show Profile  Reply with Quote
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
Go to Top of Page

jbutler003
Starting Member

USA
1 Posts

Posted - 10/16/2003 :  09:32:54  Show Profile  Reply with Quote
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

USA
3464 Posts

Posted - 10/16/2003 :  17:58:56  Show Profile  Reply with Quote
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

USA
15676 Posts

Posted - 10/16/2003 :  19:39:38  Show Profile  Visit robvolk's Homepage  Reply with Quote
I'll bet you two beers they generate the same execution plan.
Go to Top of Page

SamC
White Water Yakist

USA
3464 Posts

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

Go to Top of Page

TallCowboy0614
Starting Member

USA
17 Posts

Posted - 02/10/2004 :  20:45:15  Show Profile  Reply with Quote
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

Australia
1479 Posts

Posted - 02/10/2004 :  20:58:35  Show Profile  Reply with Quote
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

USA
17 Posts

Posted - 02/10/2004 :  20:59:59  Show Profile  Reply with Quote
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

USA
17 Posts

Posted - 02/10/2004 :  21:01:17  Show Profile  Reply with Quote
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

Australia
1479 Posts

Posted - 02/10/2004 :  21:02:33  Show Profile  Reply with Quote
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
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1631 Posts

Posted - 02/10/2004 :  21:04:10  Show Profile  Reply with Quote
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
Go to Top of Page

TallCowboy0614
Starting Member

USA
17 Posts

Posted - 02/10/2004 :  21:05:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1631 Posts

Posted - 02/10/2004 :  21:12:27  Show Profile  Reply with Quote
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

Australia
1479 Posts

Posted - 02/10/2004 :  21:18:38  Show Profile  Reply with Quote
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
Go to Top of Page

TallCowboy0614
Starting Member

USA
17 Posts

Posted - 02/11/2004 :  11:20:08  Show Profile  Reply with Quote
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

Australia
1479 Posts

Posted - 02/11/2004 :  17:19:08  Show Profile  Reply with Quote
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

United Kingdom
0 Posts

Posted - 12/09/2004 :  08:20:21  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 12/09/2004 :  09:25:45  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

USA
15676 Posts

Posted - 12/09/2004 :  09:37:51  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Australia
1591 Posts

Posted - 01/21/2005 :  01:17:17  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000