select where in - or all

Hi Experts

I have a select similar to 'select id from tbl where whatever'
i want to take this result and use in a 'where in ()
But sometimes the result might be null - and in these cases I like to retrive all rows

some like this

select  * from tbl1 where
 something = something and

 if (select id from tbl2 where....) <> NULL
   tbl1.fieldname in (select id from tbl2 where....)
 end

Any thoughts?
krogstruphedeAsked:
Who is Participating?
 
Om PrakashCommented:
Try the following:
select id, otherfields from tbl1
where (tbl1.fieldname in (select id from tbl2 where....) OR ISNULL(tbl1.fieldname,0) = 0)
0
 
Alpesh PatelAssistant ConsultantCommented:
Select * from table where ID in (Select ID from Table2)
0
 
Rajkumar GsSoftware EngineerCommented:
if exists (select 1 from table2)
  select * from table1
else
  select * from table1 where col1 in (select col1 from table2)

Open in new window


If condition checks for the existence of record and executes suitable query
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Rajkumar GsSoftware EngineerCommented:
One correction
if exists (select 1 from table2)
  select * from table1 where col1 in (select col1 from table2)
else
  select * from table1

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Another way
declare @count int
select @count=col1 from #table2 where col1 = 1
select @count
select * from #table where @count is null or col1 in (select col1 from #table2 where col1 = 1)

Open in new window


0
 
krogstruphedeAuthor Commented:
Thx guys - you gave me some ideas...
I ended with this solution - much similar to om_prakash_p's suggestion:

where id in (select id from tbl) or (select count(*) from tbl)= 0
0
 
Maxi84Commented:
I'm sorry to say that the solution you've accepted is a very, very poor one!

Although this is a popular form with developers, who may save a few lines of code in a stored procedure, it invariably leads to a table scan and dismal performance!  It is much, much better to inspect your variable(s) and/or intermediate results, and choose which query to run based on the value of that variable.

Consider the following results from an equivalent search, performed on a table with some 4k records (and a large varbinary field)

declare @var varchar(11)
set @var = '26076838007'

select * from PdfDocument
where @var is null or PdfDocumentId in (Select PdfDocumentId from PdfDocument p2 where CustomerID = @var)

Gives the following IO statistic:
Table 'PdfDocument'. Scan count 1, logical reads 7760


Compare that to:

declare @var varchar(11)
set @var = '26076838007'

if exists (Select * from PdfDocument where CustomerID = @var)
      select * from PdfDocument
      where PdfDocumentId in (Select PdfDocumentId from PdfDocument p2 where CustomerID = @var)
else
      Select * from PdfDocument


Statistics IO:
Table 'PdfDocument'. Scan count 1, logical reads 10

Of course, if your variable doesn't match you will have a table scan anyways, but hopefully most of your searches will match, and you'll get a much more efficient index seek.
0
 
krogstruphedeAuthor Commented:
Hi Maxi84

Thx for your comment and suggenstion.
Im not sure if the performance issue is a problem or not.

In my case the 2 sql (select id / select count) are both on a small temporary table.
Most times there will be less than 10 rows in the tmp table

So I figure that the perfomance wont really be a problem, since the temp table is that small?
Or am I missing something??
 
0
 
Maxi84Commented:
I agree that if it's a small table it doesn't matter.  Just beware that you do NOT use this form to query large amounts of data.

My daily headache is that I have inherited large databases where the developers have been all too fond of using this form in their queries, and this has led to some really serious performance issues in the applications.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.