Just a thought: Which statement is faster?
-
Just thought of this... which of these 2 statements are faster?
SELECT * FROM Table1 where Val IS IN ( Select v FROM Table2 )
-or-
SELECT * FROM Table1 where ( SELECT COUNT(1) FROM Table2 WHERE v = Table1.Val )
-
Hai,
Both the statements have a syntax error. Please use the forum for useful purpose
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
Everything will depend on the proper indexing. Generally, the inlist IN operator is not my favorite, although it works fine. I'm trying not to use "NOT IN" at all. "Exists/Not Exists" works much better if you could use it. The inline views are useful if you have good indexes and fresh statistics for them, but usualy with a little bit higher cost then the simple select.
Hope this helps.
MJ
-
oops, sorry, here's the correct version:
SELECT * FROM Table1 where Val IN ( Select v FROM Table2 )
-or-
SELECT * FROM Table1 where ( SELECT COUNT(1) FROM Table2 WHERE v = Table1.Val ) != 0
-
Hm.. never thought about using EXISTS. Thanks for the tip! so this is much better?
SELECT * FROM Table1 where EXISTS ( SELECT 1 FROM Table2 WHERE v = Table1.Val )
-
You cannot do this:
( SELECT COUNT(1) FROM Table2 WHERE v = Table1.Val ) != 0
- use a variable to assign it to or include the !=0 in the inner select statement.
MJ
-
huh? I just tried it and it worked...
-
Select * from objsql where exists (Select * from dbo.SysObjects) != 0
Serveur : Msg 170, Niveau 15, État 1, Ligne 1
Ligne 1 : syntaxe incorrecte vers '!'.
you can't have != 0 after the exists unless it's part of a new condition.
-
May I ask what's the purpose of this whole exercise? What are you trying to accomplish?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
SELECT * FROM Table1 where Val IN ( Select v FROM Table2 )
-or-
SELECT * FROM Table1 where ( SELECT COUNT(1) FROM Table2 WHERE v = Table1.Val ) != 0
If I'm following you correctly... why not try this?
select
t1.* from table1 le
-
SELECT * FROM Table1 where Val IN ( Select v FROM Table2 )
-or-
SELECT * FROM Table1 where ( SELECT COUNT(1) FROM Table2 WHERE v = Table1.Val ) != 0
If I'm following you correctly... why not try this?
select
t1.* from table1 le
-
SELECT * FROM Table1 where Val IN ( Select v FROM Table2 )
-or-
SELECT * FROM Table1 where ( SELECT COUNT(1) FROM Table2 WHERE v = Table1.Val ) != 0
If I'm following you correctly... why not try this?
select
t1.* from table1 le
-
Don't even ask how I did that...
I'll try again...If I'm following you correctly... why not try this?
select
t1.*
from
table1 t1, table2 t2
where
t1.val = t2.v
-
I can give you one reason.
If the number searched (t2.v) exists more than once on t2 exists only will test for the first your join will have to work on all
HTH
* Noel -
Alright - this caught my interest. I ran the following statements:
I created a test table to run the queries. The table is really basic, (ID int, varchar, varchar, int). The three columns that are not ID don't really matter, they are just taking up space. Also, there is no PK or indexes on this table.
The unique data is:
ID SomeChar SomeOtherChar SomeInteger
----------- ---------- ------------- -----------
100 CharString Whatever 3200
200 SomeString Blah 3600
300 AnotherStr BlahBlah 3800
400 Whatever character 4000
500 finstring final 4200
In the table, each is duplicated 200 times to produce 1000 total records.
I ran the following queries based on the original question (I used the same table because I wanted to return all records):
SELECT * FROM test t1 where ID IN ( Select ID FROM test )
SELECT * FROM test t1 where EXISTS ( SELECT * FROM test t2 WHERE t2.ID = t1.ID )
Suprisingly, both have the exact same access plan (path) and the "IN" clause may even take just a tad less time and resource to compute.
That seems completely wrong - right?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply