How to show a column as NULL if it doesn't exist in match?

Question

I have a query as shown below.
In this query I am trying to get the result of orders which don't have messages (which I thought as null). But I was expecting that this query would produce a NULL in message_id if someone has an order and doesn't have a message. What should I do do reach my goal?

select *, a.message_id as x
from
  messages a
  FULL OUTER JOIN customers b ON a.customer_id = b.customer_id
  FULL OUTER JOIN orders c ON c.customer_id = b.customer_id
  FULL OUTER JOIN delivery_templates d ON c.billing_template_id = d.delivery_template_id
  FULL OUTER JOIN products e ON a.product_id = e.product_id
  FULL OUTER JOIN order_items f ON f.order_id = c.order_id    
where
  a.seller_id in (2, 3, 1, 6)
  AND
  a.message_id ISNULL

Answer 1

I am trying to get the result of orders which don't have messages.

In other words: "where no message exists that is linked to the order at had". Rephrasing should help to understand the translation to the syntax in the EXISTS anti-semi-join:

SELECT *  -- or just the columns you need
FROM   orders o
WHERE  NOT EXISTS (
   SELECT FROM messages
   WHERE  customer_id = o.customer_id
   );

Contrary to your question title there is no need to display any additional null values since these are, as requested, the orders which don't have messages.

To get orders which don't have messages of a certain kind (seller_id in (2, 3, 1, 6):

SELECT *
FROM   orders o
WHERE  NOT EXISTS (
   SELECT FROM messages
   WHERE  customer_id = o.customer_id
   AND    seller_id in (2, 3, 1, 6)    -- add that condition here
   );

All the other tables you joined seem to be irrelevant to the question, but might produce additional rows.

Why?

But I was expecting that this query would produce a NULL in message_id if someone has an order and doesn't have a message. What should I do do reach my goal?

The answer is almost buried in your statement: ... this query would produce a NULL in message_id.

NULL values you get in columns of missing rows from an OUTER JOIN are filled in after the fact (produced by the query itself, not read from the table). The manual:

FULL OUTER JOIN returns all the joined rows, plus one row for each unmatched left-hand row (extended with nulls on the right), plus one row for each unmatched right-hand row (extended with nulls on the left).

The WHERE condition a.message_id ISNULL is true for two distinct cases:

1. There is no qualifying row in table a
2. There is a qualifying row in a and its column message_id holds a NULL value.

After your first predicate a.seller_id in (2, 3, 1, 6) in the WHERE clause enforces results with an existing row in messages, only rows with an actual NULL value in the column message_id would pass - which is impossible if message_id is defined NOT NULL, and contradicts your stated requirement that no such rows shall exist to begin with.

You could move the condition a.seller_id in (2, 3, 1, 6) from the WHERE clause to the join clause of an OUTER JOIN. But better use NOT EXISTS like demonstrated. Related:

• Postgres Left Join with where condition
• Select rows which are not present in other table

Answer 2

You're talking about how you display or project null when the query returns it. Remember, null is a type that represents something. It has no fixed display/rendering. For that use coalesce,

SELECT coalesce(a.message_id, 'NULL') -- or whatever you want the result is `null`

instead of

SELECT a.message_id

null For more information see the docs on COALESCE,

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display.

Answer 3

I think that your where conditions:

a.seller_id in (2, 3, 1, 6)

and:

a.message_id ISNULL

are incompatible. The first one will only work when the JOIN returns a message record; the second one only when it doesn't. You should filter on seller_id in another table (orders perhaps?)