CODEWITHSUNDEEP
sql
Adrien Gorrell
Adrien Gorrell

Reputation: 1319

SQL : select a comparison ? eg, a boolean result?

I was wondering if something like this was possible in SQL :

select (
  (select count(*) from T) = (select count(*) from T t where t.something = thing)
)

This is probably very far from the actual SQL if it is possible, I don't write database requests so often.

How could I get the result of my comparison with a single request ? Basically, if I had no time, I would just make two requests and compare the results in Java (boooooo !! I know).

Upvotes: 0

Views: 51

Answers (2)

deroby
deroby

Reputation: 6002

I would write your query like this:

SELECT (CASE WHEN (select count(*) from T) = (select count(*) from T t where t.something = thing) THEN 1 ELSE 0 END)

However, if the first T is the same as the second T then what you actually want to check is if there are any records where t.something <> thing .. right ?

In that case you could simply do :

SELECT (CASE WHEN EXISTS (select * from T t where t.something != thing) THEN 1 ELSE 0 END)

Upvotes: 0

user330315
user330315

Reputation:

Although your query should work, the following is probably faster because only a single query is needed

select total_count = thing_count
from (
   select count(*) as total_count,
          sum(case when something = 42 then 1 end) as thing_count
   from t
) t

The above is ANSI SQL and should work in any DBMS supporting a real boolean type. In Oracle you would need to use an expression in the outer select:

select case when total_count = thing_count then 1 else 0 end
from (
   select count(*) as total_count,
          sum(case when something = 42 then 1 end) as thing_count
   from t
) t

Upvotes: 2

Related Questions