Can this sql with 2 unions be minimized to no unions?

Question

I have a query that I use 2 UNIONS to get the results that I need

It may be done using a CASE statement but I could not figure it out. I will appreciated it if some can can solve this problem

This is my current query please keep in mind that viewable_by can be only 1, 2 or 3 However, client_id, team_id and created_by will be a variable depending on the user those will change

SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
WHERE status = 1 AND ( viewable_by = 1  AND client_id = 1)
UNION
SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
WHERE status = 1 AND ( viewable_by = 2  AND team_id =5 AND client_id = 1)
UNION
SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
WHERE status = 1 AND ( viewable_by = 3  AND created_by= 6)

This is my actual query with the variable in php

$db->getDataSet(' SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
                                     WHERE status = 1 AND ( viewable_by = 1  AND client_id = '.CLIENT_ID.')
                                     UNION
                                     SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
                                     WHERE status = 1 AND ( viewable_by = 2  AND team_id ='.TEAM_ID.' AND client_id = '.CLIENT_ID.')
                                     UNION
                                     SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
                                     WHERE status = 1 AND ( viewable_by = 3  AND created_by= '.USER_ID.') '
                                    );

Why do you not want to use UNION? – Mike Purcell Apr 03 '13 at 00:18 — Mike Purcell, Apr 03 '13 at 00:18

Daniel Williams · Answer 1 · 2013-04-03T00:12:35.057

1

SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
WHERE status = 1 AND ( (viewable_by = 1  AND client_id = 1) OR 
( viewable_by = 2  AND team_id =5 AND client_id = 1) OR ( viewable_by = 3  AND created_by= 6))

Is the easiest way to do it

Also, with mysql, remember that UNION will remove duplicates and this will not.

If you can limit your logical constraints to the same set of columns for each query you can replace all the OR's and UNION's with WHERE ROW(all your columns) IN (all your values). But given that you have very different requirements per query, I think the OR's are your best bet.

edited Apr 03 '13 at 00:12

answered Apr 02 '13 at 23:58

Daniel Williams

8,673
4
36
47

ORs are nasty and should be avoided at all costs. – Mike Purcell Apr 02 '13 at 23:59
Having worked with them many moons ago, we found them to be non-performant and hard to optimize. – Mike Purcell Apr 03 '13 at 00:00
@MikePurcell, How can an operator be non-performant? And why would you want to optimize `OR`? – Starx Apr 03 '13 at 00:07
Sorry, didn't mean to be so abstract with my previous comment. I just remember that when we were working to reduce our Queries Per Second (qps), several of the 'slow' queries (>2s) included ors. I'm sure a DBA can explain in more detail, but this is a good read too: http://stackoverflow.com/questions/13750475/sql-performance-union-vs-or – Mike Purcell Apr 03 '13 at 00:08
@MikePurcell, I am sure Or is a lot better than having 2 unions – Jaylen Apr 03 '13 at 00:09
@MikePurcell, Looking at the situation, I dont see any other way to solves this problem without the `UNION`. – Starx Apr 03 '13 at 00:14
@Starx: If the OP wants to move away from Unions, I agree. Just curious why he doesn't want to use Union. – Mike Purcell Apr 03 '13 at 00:21

Starx · Accepted Answer · 2013-04-03T00:16:22.567

1

You can combine them like this:

SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
WHERE status = 1 AND (
   (viewable_by = 1  AND client_id = 1) or 
   (viewable_by = 2  AND team_id =5 AND client_id = 1) or 
   (viewable_by = 3  AND created_by= 6)
)

You can also use SELECT .... WHERE IN statement to combine the OR statements.

Like

SELECT * FROM ... WHERE viewable_by IN (1,2,3)

Will translate to

SELECT * FROM ... WHERE viewable_by='1' or viewable_by='2' or viewable_by='3'

But IN clauses I believe while logically equivalent to OR's are not at all the same when it comes to query execution

edited Apr 03 '13 at 00:16

answered Apr 02 '13 at 23:59

Starx

77,474
47
185
261

1

IN clauses I believe while logically equivalent to OR's are not at all the same when it comes to query execution. – Daniel Williams Apr 03 '13 at 00:05
@DanielWilliams, Very true. I should have added this to the answer. – Starx Apr 03 '13 at 00:11
Agree with Daniel Williams. No way you could convert this query to using IN and still maintain the AND conditions separated by each OR. – Mike Purcell Apr 03 '13 at 00:11
@MikePurcell, I never said it could :) – Starx Apr 03 '13 at 00:12
"You can also use SELECT .... WHERE IN statement to combine the OR statements." – Mike Purcell Apr 03 '13 at 00:12
@MikePurcell, Yes Mike, `to combine the OR Statements` that does not mean, combine the `OR` statement and `AND` statement as in the questions. My last edit should fix this confusion though. – Starx Apr 03 '13 at 00:15

Can this sql with 2 unions be minimized to no unions?

2 Answers2