问题:

Let's say I have a table called references, which has two fields: an id and a reference field.

I want to create a query that will provide me with a reference number based upon an id. Like this:

SELECT reference 
  FROM references 
 WHERE id = x

(where x is some integer)

However if the id is not found in the table I would like the query to show -1 instead of NULL.

How can I do this?

SELECT COALESCE(reference, -1) FROM references WHERE id = x

doesn't work

回答1:

Here are a few approaches:

SELECT COALESCE(MAX(reference), -1)
  FROM references
 WHERE id = ...
;

SELECT COALESCE(reference, -1)
  FROM references
 RIGHT
 OUTER
  JOIN (SELECT 1 c) t
    ON id = ...
;

SELECT COALESCE
        ( ( SELECT reference
              FROM references
             WHERE id = ...
          ),
          -1
        )
;

(I'd go with the first one, personally, but all three work.)

回答2:

if subset has cardinality 0 (elements with id = 2), there is nothing to compare, there's certainity that such (id = 2) element doesn't exist. in the other hand, if you want to find, let's say, maximum element in that empty subset, you will get unknown value (every member of superset will be a supremum and infimum of the empty set)

i'm not sure if it's correct, but imho, quite logical