I have a table that create the relationship between users and groups that looks like:
UserGroups
group_id | user_id |
---|---|
1 | 1 |
2 | 1 |
1 | 2 |
1 | 3 |
and the table users
looks like:
id | name |
---|---|
1 | John |
2 | Mary |
3 | Bob |
4 | Carol |
And I’m successfully selecting my results like this:
SELECT
...,
CASE
WHEN users.id in (SELECT user_id from usergoups where group_id = 1) and not in (SELECT user_id from usergoups where group_id = 2) then 1
WHEN users.id in (SELECT user_id from usergoups where group_id = 2) and not in (SELECT user_id from usergoups where group_id = 1) then 2
WHEN users.id in (SELECT user_id from usergoups where group_id = 1) and in (SELECT user_id from usergoups where group_id = 2) then 1
else class.group
end as class_group
...
LEFT JOIN users on users.id = 1
for the following sample output:
class_id | class_group |
---|---|
1 | 1 |
2 | 1 |
3 | 1 |
4 | 1 |
I was wondering if it is possible to simplify the select and make it more efficient reducing the number of selections, as i could just have something like:
...
LEFT JOIN usergroups on usergroups.user_id = users.id
...
The only line that i could think of was:
...
WHEN 2 in usergroups then...
but it just trows a sintax error as i kinda expected
2
Answers
We could avoid correlated subqueries in expressions in the SELECT list, if we instead use an inline view (mysql calls it a derived table) to check for the existence of rows in
user_groups
. Following this line in the outer queryI’d add an outer join to an inline view query something like this:
The view query will return one (or zero) rows for each
user_id
in theuser_groups
table, so it flattens toFor improved performance, if the outer query is restricting results to just one user, e.g.
We can include a matching restriction in the inline view query,
In the SELECT list of the outer query, we can do the conditional checks,
Note that the first
WHEN
is redundant. If the first WHEN condition is TRUE, then we know the second WHEN condition cannot be TRUE, so we could check that first, and then the third WHEN condition covers the first. This would return an equivalent result,If we weren’t needing to return
class.group
from the CASE expression, I’d be more tempted to move the logic down into the inline view. (We could move the checks for group_id 2 and group_id 1, and return 2 or 1 or NULL to the outer query, but we’d still need an expression to check the return from g to see if we return that, or returnclass.group
Another way to avoid correlated subqueries in the SELECT list, the syntax is a bit cleaner than the approach in my other answer,
BUT we would only do this…
IF (and only IF) we have some guarantee that rows in
user_groups
are UNIQUE i.e. if theres aPRIMARY KEY
orUNIQUE KEY
constraint on the(group_id,user_id)
tuple (or it could be the other way around(user_id,group_id)
)THEN we could use two outer joins to the
user_groups
tableSo following this line:
We could add
Then in our expression in the SELECT list could be
NOTE: in MySQL, when a numeric is evaluated as a boolean, a value of 0 (zero) is FALSE, any non-zero value is TRUE, and NULL is NULL.
So the above is equivalent shorthand for
N.B. without guaranteed uniqueness, the JOINs have potential to match multiple rows from
user_groups
(e.g. consider if there are two rows(1,1),(1,1)
), that would double the number of rows returned, which we probably do not want)The approach in my other answer is not subject to the same uniqueness requirement; any duplicate rows for a
(user_id,group_id)
will be collapsed into a single row for theuser_id
by theGROUP BY
in the inline view.