SELECT clause using INTERSECT

Question

I have a DEPTLOC table like this:

//DEPTLOC
DNAME              CITY
------------------------
RESEARCH          BOSTON
   IT             LONDON
 SCIENCE          BOSTON
SEARCHING         NEWYORK

I used the following query:

SELECT CITY FROM DNAME WHERE DNAME='RESEARCH'
INTERSECT
SELECT CITY FROM DNAME WHERE DNAME='SCIENCE'

So this returns a result, for example:

CITY
---------
BOSTON

But how can I change the code so that if any result is found, it will display NO, otherwise it will display YES

The result should be something like this:

RESULT
---------
YES

or

RESULT
---------
NO

Answer 1

What you want to do is count your results.

SELECT
  CASE WHEN COUNT(*) = 0 THEN 'Yes' ELSE 'No' END
FROM
(
  SELECT CITY FROM DNAME WHERE DNAME='RESEARCH'
  INTERSECT
  SELECT CITY FROM DNAME WHERE DNAME='SCIENCE'
);

Of course, there are other ways to achieve the same result. This demonstrates how to use your INTERSECT query for this.

Answer 2

You can do this as shown below using COUNT()andDECODE/CASE

SELECT DECODE(COUNT(1),0,'NO','YES') AS RESULT FROM 
(
  SELECT CITY FROM DNAME WHERE DNAME='RESEARCH'
  INTERSECT
  SELECT CITY FROM DNAME WHERE DNAME='SCIENCE'
)