MySql Select columns from same table based on different condition

Question

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.

Dan Goslen · Accepted Answer · 2018-06-13 13:41:19Z

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.

answered Jun 13, 2018 at 13:41

Dan Goslen

1166 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pranav Over a year ago

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?

Dan Goslen Over a year ago

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. From my experience, OR statements tend to work better than multiple result-sets using UNION or UNION ALL

Collectives™ on Stack Overflow

MySql Select columns from same table based on different condition

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related