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. May 14 '18 at 6:59

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. May 14 '18 at 6:58
  • Yes, I'm unable to guess what would filter out certain rows so I assumed that the expected result is uncomplete – Lennart May 14 '18 at 7:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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