Optimization for SQL Select with Union

Question

I have the following SQL query, in which I make use of UNIONs:

SELECT notification FROM NotificationDb notification WHERE notification.proc_name = 'a' AND notification.status = 1 AND notification.module = 1
UNION
SELECT notification FROM NotificationDb notification WHERE notification.proc_name = 'b' AND notification.param1 >0 AND notification.status = 1 AND notification.module = 1
UNION
SELECT notification FROM NotificationDb notification WHERE notification.proc_name = 'b' AND notification.param1 <= 0 AND notification.status = 1 AND notification.module = 1

I have it set up this way, because I need them ordered by proc_name field and also param1 value for cases where proc_name is 'b'

QUESTION: Is there a better, a more efficient way of putting together a query that would return the same result?

Any help would be appreciated!

Yes, there are better ways. Only one SELECT needed. OR the 3 WHERE clauses' conditions. Do you need SELECT DISTINCT or just SELECT? — jarlh
– jarlh, Commented Jun 1, 2021 at 9:04

Tim Biegeleisen · Accepted Answer · 2021-06-01 09:08:32Z

2

If I understand correctly, you don't need unions here, you can just do a single distinct select:

SELECT DISTINCT notification
FROM NotificationDb
WHERE status = 1 AND module = 1 AND proc_name IN ('a', 'b')
ORDER BY
    proc_name,
    param1 DESC;

Here, we add an ORDER BY clause which puts a proc_name records before b. Then, within all b records, we show larger param1 records first. This sorting maintain the perceived ordering you have in current union query (though it should be noted that no such actual order is there; always use an ORDER BY clause if you expect a certain sorting in your result set).

answered Jun 1, 2021 at 9:08

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

Gordon Linoff Over a year ago

This should fail because proc_name is not defined due to the distinct. I am also baffled at this being accepted, because the logic does not look the same as the code in the question. Perhaps something changed.

Tim Biegeleisen Over a year ago

@GordonLinoff Now I'm also wondering what the OP's database is (perhaps something like MySQL which allows it to run).

Collectives™ on Stack Overflow

Optimization for SQL Select with Union

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related