Merge two rows with both possibly being null

Question

In PostgreSQL, I have a table with some settings the user can make. In that table there is also a "global" user that takes place if the user doesn't have any settings for them.

This query returns the settings for the user:

SELECT FIELD_1, FIELD_2, FIELD_3, FIELD_XML
FROM   SETTINGS
WHERE  FIELD_USR = :MY_USR AND FIELD_TYPE = :FIELD_TYPE

This one returns the settings (only the XML actually) for the "global" user:

SELECT FIELD_XML AS GLOBAL_XML
FROM   SETTINGS
WHERE  FIELD_USR = :GLOBAL_USR AND FIELD_TYPE = :FIELD_TYPE

What I want is a query that returns a single row with FIELD_1, FIELD_2, FIELD_3, FIELD_XML, GLOBAL_XML.

The problem is that either of these queries could return null, so my attempt at using a subquery fails:

SELECT FIELD_1, FIELD_2, FIELD_3, FIELD_XML,
       (SELECT FIELD_XML
        FROM   SETTINGS
        WHERE  FIELD_USR = :GLOBAL_USR AND FIELD_TYPE = :FIELD_TYPE) AS GLOBAL_XML
FROM   SETTINGS
--Fails if the user doesn't have a setting. Swapping fails if there is no global user.
WHERE  FIELD_USR = :MY_USR AND FIELD_TYPE = :FIELD_TYPE

The only way I managed to get the result I wanted was to use a FULL JOIN with subqueries:

SELECT A.FIELD_1, A.FIELD_2, A.FIELD_3, A.FIELD_XML, B.FIELD_XML AS GLOBAL_XML
FROM   (SELECT FIELD_1, FIELD_2, FIELD_3, FIELD_XML
        FROM   SETTINGS
        WHERE  FIELD_USR = :MY_USR AND FIELD_TYPE = :FIELD_TYPE) A
       FULL JOIN 
       (SELECT FIELD_XML
        FROM   SETTINGS
        WHERE  FIELD_USR = :GLOBAL_USR AND FIELD_TYPE = :FIELD_TYPE) B ON B.FIELD_TYPE = A.FIELD_TYPE

This returns me FIELD_1, FIELD_2, FIELD_3, FIELD_XML (possibly being null) and GLOBAL_XML (also possibly being null). But is there a better way to do this?

Answer 1

Select both User and Global and take 1

select * from
(
  SELECT FIELD_1, FIELD_2, FIELD_3, FIELD_XML
         ,'1' ordN,'User' src
  FROM   SETTINGS
  WHERE  FIELD_USR = :MY_USR AND FIELD_TYPE = :FIELD_TYPE
  union all
  SELECT  FIELD_1, FIELD_2, FIELD_3, FIELD_XML
         ,'1' ordN,'Global' src
  FROM   SETTINGS
  WHERE  FIELD_USR = :GLOBAL_USR AND FIELD_TYPE = :FIELD_TYPE
) x
order by ordN
limit 1

Answer 2

If we can assume each query only returns zero or one record use a cross join.

Not sure if this is cleaner/more elegant... I generally find this easier to read/ maintain; but others may not.

Basically write both queries as a CTE then cross join.

WITH 
S1 as (
  SELECT FIELD_1, FIELD_2, FIELD_3, FIELD_XML
    FROM SETTINGS S1
   WHERE S1.FIELD_USR = :MY_USR 
     AND S1.FIELD_TYPE = :FIELD_TYPE),

S2 as (SELECT FIELD_XML
         FROM SETTINGS
        WHERE FIELD_USR = :GLOBAL_USR 
          AND FIELD_TYPE = :FIELD_TYPE)

SELECT S1.FIELD_1, S1.FIELD_2, S1.FIELD_3, S1.FIELD_XML, S2.FIELD_XML  AS GLOBAL_XML
FROM S1
CROSS JOIN S2