CODEWITHSUNDEEP
sql
Ram
Ram

Reputation: 1064

SQL: Determine the record count for 2 separate filter conditions in the execution of only 1 query

Let's say I have a BOOK table.

with this structure:

create table BOOK (
  AUTHOR varchar2(100),
  TITLE  varchar2(100)
);

What I want to do is to check/(or count, count(*) = 1) whether a book with author = 'John Smith' exists, and whether there exists a book authored by 'John Smith' starting with the letter 'T'.

I came up with two SQL statements.

select count(1)
  from BOOK
 where AUTHOR = 'John Snow'
   and rownum = 1

select count(1)
  from BOOK
 where AUTHOR = 'John Snow'
   and TITLE like = 'T%'
   and rownum = 1

My question is: Is there a way such that I can know whether a book of such criteria exists (I need to know whether there is a book by John Snow and whether he wrote a book with a title starting with the letter 'T') by performing only 1 query? Such that performing this 1 query will be faster than performing the 2 queries from above? Thanks!

Upvotes: 0

Views: 218

Answers (2)

gbn
gbn

Reputation: 432261

select
    SIGN(count(*)),
    SIGN(count(CASE WHEN TITLE like 'T%' THEN 1 ELSE NULL END)),
from BOOK
where AUTHOR = 'John Snow';

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Well, you can put the information in two columns:

select (case when sum(case when AUTHOR = 'John Snow' then 1 else 0 end) > 0
             then 1
             else 0
        end) as AuthoredByJohnSnow,
       (case when sum(case when AUTHOR = 'John Snow' and Name like 'T%' then 1 else 0 end) > 0
             then 1
             else 0
        end) as AuthoredByJohnSnowBookT
from Book

You can make this more efficient by putting the AUTHOR = 'John Snow' in the where clause. This explicitly doesn't do that, so you can easily add more factors in the select.

Upvotes: 2

Related Questions