Query to determine if a certain value exists -- sql server new users

Adam Machanic
2/17/2005 9:19:51 PM

IF EXISTS
(SELECT *
FROM YourTable
WHERE SomeCol = 'x')
BEGIN
PRINT 'Value x exists'
END

.... or maybe ..

SELECT 'Value x exists' AS DoesItExist?
WHERE EXISTS
(SELECT *
FROM YourTable
WHERE SomeCol = 'x')

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

[quoted text, click to view]

Daryl Muellenberg
2/18/2005 8:42:25 AM

Just what I need - thanks.

Daryl

[quoted text, click to view]

Daryl Muellenberg
2/18/2005 12:36:29 PM

I don't think your suggestion will do what I want because the subselect will
still search the entire table to find all rows that contain the value I'm
looking for. While it will a true/false condition it is still searching the
entire table.

[quoted text, click to view]

Adam Machanic
2/18/2005 2:03:26 PM

[quoted text, click to view]

No it won't -- it will stop as soon as it finds a single row. That's the
behavior of EXISTS.

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Wayne Snyder
2/22/2005 8:00:33 AM

Using Exists is the better answer to your question. It stops immediately
when the First row that satisfies the query is found.,

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

[quoted text, click to view]

Walter Clayton
2/23/2005 9:47:59 AM

Sure about that? Explain indicates otherwise.

Although a properly coded 'where exists' does stop as soon as something
satisfies the where clause, there is only 1 row passed through all stages
when using 'top 1'.

--
Walter Clayton
Any technology distinguishable from magic is insufficiently advanced.
http://www.dts-l.org

[quoted text, click to view]

Adam Machanic
2/24/2005 11:09:26 AM

[quoted text, click to view]

What do "properly coded" and "passed through all stages" mean? I'm a
bit perpelxed as to your opinion; the behavior of EXISTS is well documented.
The presence of any rows (= 1 row) satisfying the condition will stop the
query engine from having to find any more rows. There is no way to
improperly code this or break the behavior; it works the same way every
time.

Take a look at the execution plan output by the following batch, which
shows that there is no scan of the table abc -- the query engine stops as
soon as it finds a single row -- the row from the 'select 1'.

use tempdb
go

create table abc(id int)
go

insert abc values (1)
go

set statistics profile on

select 1
where exists
(select 1
union all
select id
from abc)
go

drop table abc
go

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Walter Clayton
2/24/2005 10:38:36 PM

:-)

Again, are you sure that 'top 1' is such a bad thing compared to 'when
exists'?

Code these two and check the execution plan against both. These are database
independent so no need for any temp tables, etc.. These simply hit the
master table.

select 1
where (exists (
select *
from master..syscolumns
where id = 1
) )
;

select top 1 id
from master..syscolumns
where id = 1
;

Now, here's where properly coded comes into play since I keep forgetting
that SQL Server doesn't require a 'from' (I bounce between SQL Server,
TeraData and DB2).

select 1
from master..syscolumns
where (exists (
select *
from master..syscolumns
where id = 1
) )
;

So why is 'when exists' so much better from a performance standpoint?

--
Walter Clayton
Any technology distinguishable from magic is insufficiently advanced.

[quoted text, click to view]

Adam Machanic
2/25/2005 9:14:08 AM

[quoted text, click to view]

A) "TOP 1" is nondeterministic without an accompanying ORDER BY
clause -- this means that EVERY ROW must be accounted for in order to get a
meaningful result.

B) "TOP 1" is proprietary -- which only matters since you're such a
multi-DBMS kinda guy.

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Walter Clayton
2/25/2005 6:07:30 PM

:-)
[quoted text, click to view]

Not according to existing documentation and request analyzer. There's no
'with ties'.

So you code:

select top 1 1
from table
where (col = 'val')
order by col
;

The DB2 equivalent is
select 1
from table
where (col = 'val')
order by col
fetch first 1 row only
;

[quoted text, click to view]

There's quite a bit of stuff that DB2 and SQL Server implement that are
conceptually similar yet syntactically different. In fact one of the reasons
why I will use the syntax to limit the number of returned rows is in fact
due to the differences in SQL implementations.
e.g. there is no syntactical equivalent to

select 1
where (exists (
select 1
from tab
where (col = 'val')
) )
;

The closest in DB2 is
select 1
from tab
where (exists (
select 1
from tab
where (col = 'val')
) )
;
...which has some rather obvious problems.

But you're also correct in that Teradata, at present, lacks any syntax to
return a set number of rows.

--
Walter Clayton
Any technology distinguishable from magic is insufficiently advanced.

[quoted text, click to view]

Adam Machanic
2/26/2005 11:12:51 AM

[quoted text, click to view]

I'm not at all familiar with Teradata. Perhaps you can do something like
this:

SELECT
T1.ColA,
T1.ColB,
T1.ColC
FROM Tbl AS T1
JOIN Tbl AS T2 ON T2.PK <= T1.PK
GROUP BY
T1.ColA,
T1.ColB,
T1.ColC
HAVING COUNT(*) = 1;

.... Assuming that PK is unique, this query will give you the "TOP 1" row
ordered by PK. If PK is nonunique you'd have to add a second column to
break ties... This isn't exactly an efficient technique, but it works for
DBMSs that don't have TOP or FIRST extensions.

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Walter Clayton
2/26/2005 12:13:20 PM

I'll have to try that when I get a chance next week. I've had several folks
on the project where I'm currently on contract ask what the equivalent
'top'/'first' function for Teradata was and even though I've only been hands
on with Teradata a bit less than a year I've got a reputation for finding
SQL centric solutions for any platform (except Jet). :-/

Just haven't considered limiting things via join.

Efficiency on Teradata is of secondary concern to a degree. Only becomes are
a consideration once the database exceeds a few million rows.

--
Walter Clayton
Any technology distinguishable from magic is insufficiently advanced.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

sql server new users : Query to determine if a certain value exists