How to set a default return value if a row doesn't exist-CodePudding

Say I have two tables:

Users - UserID, UserName
Actions - UserID

The database doesn't have referential integrity, and so If a user has been deleted, the Actions table can still contain the User ID.

I am running a query like:

SELECT DISTINCT UserName FROM USERS u WHERE u.UserId IN (SELECT DISTINCT UserID FROM Actions)

what I want is to be able to adjust the query to say "but, if the user ID doesn't exist in the users table, default to --- instead".

If there was just one value I was searching for, I could do it with a UNION SELECT '---' FROM DUAL WHERE NOT EXISTS (value), but if I try that here they would have to all be missing in order for it to work.

How can I change the query to get the results I want?

CodePudding user response：

I think this is what you're looking for and what you want can be achieved with table aliases:

SELECT DISTINCT U.USERNAME
  FROM USERS U
 WHERE U.USERID IN (SELECT DISTINCT USERID 
                      FROM ACTIONS)
 UNION 
SELECT '---'
  FROM ACTIONS A
 WHERE NOT EXISTS (SELECT 1
                     FROM USERS
                    WHERE USERID = A.USERID);

CodePudding user response：

If you are trying to display a default value when no users/actions are matched then, from Oracle 12, you can use:

SELECT username
FROM   (
  SELECT DISTINCT
         1 AS priority,
         UserName
  FROM   USERS
  WHERE  UserId IN (SELECT DISTINCT UserID FROM Actions)
  UNION ALL
  SELECT 2, '---' FROM DUAL
  ORDER BY Priority
  FETCH FIRST ROW WITH TIES
);

In earlier versions, you can use:

SELECT username
FROM   (
  SELECT username,
         RANK() OVER (ORDER BY priority) AS rnk
  FROM   (
    SELECT DISTINCT
           1 AS priority,
           UserName
    FROM   USERS
    WHERE  UserId IN (SELECT DISTINCT UserID FROM Actions)
    UNION ALL
    SELECT 2, '---' FROM DUAL
  )
)
WHERE  rnk = 1;

If you are just trying to JOIN the two tables and fill in the blanks then:

SELECT DISTINCT
       COALESCE(u.username, '---') AS username
FROM   actions a
       LEFT OUTER JOIN users u
       ON (a.userid = u.userid)