Given a table like:

object    subject    ID      parentID
------    -------   -----    --------
  A         NULL      1          0
  B         NULL      2          0
  C         NULL      3          0
  D         NULL      4          0
  E         NULL      5          0
 Null        CC       6          1
 Null        DD       7          2
 Null        EE       8          2

I need a query with good performance that returns the following result:

  • Please explain what this query should do; in particular, why C/D/E are not returned, and how deep the recursion can go.
    – CL.
    Commented May 14, 2018 at 6:59

1 Answer 1


You can use coalesce, it's a function that returns its leftmost non-null argument:

select coalesce(object, subject) 
from t;

Coalesce is a shorter form of:

select case when object is not null then object else subject end
from t; 
  • This query returns eight rows.
    – CL.
    Commented May 14, 2018 at 6:58
  • Yes, I'm unable to guess what would filter out certain rows so I assumed that the expected result is uncomplete Commented May 14, 2018 at 7:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.