用于case语句的sql重写

Question

我正在使用case语句，并想知道是否有更好的方法来编写它？

这就是我目前所拥有的，它是有效的：

select 
CASE WHEN ug.permissions = "" OR ug.permissions IS NULL
THEN
    g.permissions
ELSE
     ug.permissions 
END as permissions,
CASE WHEN ug.display = "" OR ug.display IS NULL
THEN
    g.display
ELSE
     ug.display 
END as display
from system.users_groups as ug
inner join system.groups as g on ug.group_id = g.id
where ug.user_id = ?

我在想更多类似的东西，但我对sql中的case语句几乎没有经验：

select 
CASE WHEN ug.permissions = "" OR ug.permissions IS NULL
THEN
    g.permissions,
    g.display
ELSE
    ug.permissions,
    ug.display
END
from system.users_groups as ug
inner join system.groups as g on ug.group_id = g.id
where ug.user_id = ?

Answer 1

我更喜欢以下内容：

SELECT
  g.id,
  IFNULL(NULLIF(ug.permission, ''), g.permission),
  IFNULL(NULLIF(ug.display, ''), g.display)
FROM groups AS g

INNER JOIN users_group AS ug
ON ug.group_id = g.id

SQLFiddle