# How to check if a subquery has exactly one distinct result and a specified value concisely?

I found myself writing the following:

``````select 'yes'
where exists(select * from foo where val=1)
and not exists(select * from foo where val<>1);
``````

and wondering if there is a more concise way without sacrificing too much readability.

I found one way which I am posting as an answer but I'm not entirely happy with it and would be very interested in alternatives

In this case `val` is unique within `foo` - there are no duplicates

-
Do I understand correctly that you want exactly one row in the result of the subquery? –  Erwin Brandstetter Sep 4 '12 at 16:03
Which subquery? –  Jack Douglas Sep 4 '12 at 17:40
The one you mention in the title. I wasn't sure whether it should be one result after or before "distinct". –  Erwin Brandstetter Sep 4 '12 at 18:00
Ah yes, that one :) I was rather confusingly referring to the sub-query in my answer - yours is far more specific and flexible, eg you can also use `count(distinct val)`, though in my real-world case it makes no difference –  Jack Douglas Sep 4 '12 at 21:05

Concise, fast (especially with many rows), my favorite concerning readability and would work with dupes, too:

``````SELECT count(*) = 1 AND min(val) = 1 FROM foo;
``````

Returns `TRUE` / `FALSE` .. or `NULL` - only in the case of exactly one row with `val IS NULL`, because `count()` never returns `NULL` or no row.

The second `1` in the example just happens to be the same as the first, because of your example.

The query in the question fails with `NULL` values. Consider the simple demo:

``````CREATE TABLE foo (id int, val int);
INSERT INTO foo VALUES (1, 1),(2, NULL);

SELECT 'yes'
WHERE      EXISTS(SELECT * FROM foo WHERE val =  1)
AND    NOT EXISTS(SELECT * FROM foo WHERE val <> 1);
``````

`IS DISTINCT FROM` would fix this, but it could still fail with duplicates in `val` - which you have ruled out for this case.

Returns `'yes'` / no row.

I would prefer this shorter form, though. Don't forget that PostgreSQL (unlike Oracle) has a proper `boolean` type.

``````SELECT array_agg(val) = array[1] FROM foo;
``````

Returns `TRUE` / `FALSE` / `NULL`.

-
excellent, thanks, I knew there would be a better way :) –  Jack Douglas Sep 4 '12 at 17:38

A variation on @Erwin's answer. No `COUNT()` at all, only `MIN()` and `MAX()`. It may be slightly more efficient with big table and (not in your case) duplicate `val`:

``````SELECT MIN(val) = 1 AND MAX(val) = 1 FROM foo;
``````
-
+1 thanks. It handles nulls and duplicates differently of course (if there were any) –  Jack Douglas Sep 5 '12 at 12:07
@Jack: Yes. Does your table have nulls? Or you want answers for both cases (with and without)? –  ypercube Sep 5 '12 at 12:12
no mine doesn't - I can use either :) –  Jack Douglas Sep 5 '12 at 14:14
Would be much faster on bigger tables with a matching index, but performs identically in the absence of such an index - like when testing query results. –  Erwin Brandstetter Sep 5 '12 at 18:47
``````select 'yes' where (select array_agg(val) from foo)=array[1];
``````
-

This one returns `true`, `false` or an empty result:

`````` select j.val is null
from foo left join foo as j on j.val <> foo.val
where foo.val = 1 limit 1;
``````
-
at first glance, this doesn't seem to return `false` if there are values in `foo` where `val<>1`? –  Jack Douglas Sep 5 '12 at 7:42
@JackDouglas Oh, sorry. I understood the task wrong the first time. Fixed. –  grayhemp Sep 5 '12 at 8:51
Works - except with `NULL` as value which has not been ruled out in this case. –  Erwin Brandstetter Sep 5 '12 at 12:31
@ErwinBrandstetter `NULL` can be worked out by using `IS [NOT] DISTINCT FROM` I think. –  grayhemp Sep 8 '12 at 11:05
@grayhemp: Not in this case. `LEFT JOIN foo j ON j.val <> foo.val` fails to detect a row with `j.val IS NULL` to begin with. If you'd include it with `ON j.val IS DISTINCT FROM foo.val` you'd then need to check on another column of `j` defined `NOT NULL` to tell the two cases apart. But no additional column is defined. –  Erwin Brandstetter Sep 8 '12 at 13:30