0

I have to run multiple selects on a table for and get resultset for 2 columns. One solution i found is by using UNION as below.

SELECT cap_id, cap_code FROM cap_master
where cap_type = 'Type1' and cap_desc = 'CapDesc1' 
 UNION
SELECT cap_id, cap_code FROM cap_master
where cap_type = 'Type2' and cap_desc = 'CapDesc2'

Is there any other way to do this. There could be some 10-20 select statements in one go. Will this affect performance, if so what would be a better approach.

pranav
  • 421
  • 1
  • 11
  • 27

1 Answers1

1

I think you should just be able to use one query with a larger WHERE clause using OR statements.

Example

SELECT cap_id, cap_code
FROM cap_master
WHERE (cap_type = 'Type1' AND cap_desc = 'CapDesc1`) 
    OR (cap_type = 'Type2' AND cap_desc = 'CapDesc2')

That is a least a starting point for getting results for only when the cap_type and cap_desc are specific values.

Dan Goslen
  • 106
  • 5
  • Thanks Dan!! This will reduce my query a lot. Any idea, will this cause any performance overhead if have have 10-20 conditions ? If yes, what is the better approach? – pranav Jun 13 '18 at 13:47
  • I don't know your use case, but it would seem having those conditions means you aren't using your query in the right place within your solution. I can't speak to a better approach there. I do know `UNION` will try to sort through duplicates, which you shouldn't have. There is another question that digs into performance between the two query options that you can view [here](https://stackoverflow.com/questions/13750475/sql-performance-union-vs-or). From my experience, `OR` statements tend to work better than multiple result-sets using `UNION` or `UNION ALL` – Dan Goslen Jun 15 '18 at 15:00