Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

select where in - or all

Posted on 2011-05-12
9
Medium Priority
?
346 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:krogstruphede
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35745619
Select * from table where ID in (Select ID from Table2)
0
 
LVL 22

Accepted Solution

by:
Om Prakash earned 1000 total points
ID: 35745620
Try the following:
select id, otherfields from tbl1
where (tbl1.fieldname in (select id from tbl2 where....) OR ISNULL(tbl1.fieldname,0) = 0)
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35745631
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35745642
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35745685
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
 

Author Comment

by:krogstruphede
ID: 35745744
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
 
LVL 4

Expert Comment

by:Maxi84
ID: 35746003
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
 

Author Comment

by:krogstruphede
ID: 35746071
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
 
LVL 4

Expert Comment

by:Maxi84
ID: 35746259
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question