INTERSECT only when the 2nd query has results

HanbingL · #1 (**permalink**) 05-18-10, 11:22

Hi I have 2 queries and I want to make them into 1 to achieve the following result:

Code:

________________________
Case 1:
Query1      Query2
---------   -------
1               1

Result
--------
1
________________________
Case 2:
Query1      Query2
---------   -------
1               2

Result
--------
________________________
Case 3:
Query1      Query2
---------   -------
1               

Result
--------
1
_________________________

i've tried

Query1
INTERSECT
Query2
only works in case 1 and 2. how do I make it to work for query 3?

In sql server i might try

IF EXISTS(QUERY2)
THEN
QUERY1
INTERSECT
QUERY2
ELSE
QUERY1
END

But the above is not working for me in db2.

Thanks in advance!

dav1mo · #2 (**permalink**) 05-18-10, 11:39

maybe I am oversimplifying, but isn't this what a left outer join is for? Hard to tell not seeing your SQL and all.
Dave

tonkuma · #3 (**permalink**) 05-18-10, 12:28

Maybe.....

Code:

Query1
INTERSECT
(
Query2
UNION ALL
SELECT *
  FROM (Query1) q
 WHERE NOT EXISTS(Query2)
)

tonkuma · #4 (**permalink**) 05-18-10, 12:57

For example:
(Used "SELECT * FROM Query1" instead of simple "Query1", because of using common table expressions.)

Query2 exists

Code:

------------------------------ Commands Entered ------------------------------
WITH
 Query1 AS (
SELECT empno , firstnme , workdept , sex
  FROM employee
 WHERE workdept = 'D11'
)
,Query2 AS (
SELECT empno , firstnme , workdept , sex
  FROM employee
 WHERE sex = 'F'
)
SELECT * FROM Query1
INTERSECT
(
SELECT * FROM Query2
UNION ALL
SELECT * FROM Query1
 WHERE NOT EXISTS
       (SELECT 0 FROM Query2)
)
;
------------------------------------------------------------------------------

EMPNO  FIRSTNME     WORKDEPT SEX
------ ------------ -------- ---
000160 ELIZABETH    D11      F  
000180 MARILYN      D11      F  
000220 JENNIFER     D11      F  

  3 record(s) selected.

Query2 not exists

Code:

------------------------------ Commands Entered ------------------------------
WITH
 Query1 AS (
SELECT empno , firstnme , workdept , sex
  FROM employee
 WHERE workdept = 'D11'
)
,Query2 AS (
SELECT empno , firstnme , workdept , sex
  FROM employee
 WHERE sex = 'X'
)
SELECT * FROM Query1
INTERSECT
(
SELECT * FROM Query2
UNION ALL
SELECT * FROM Query1
 WHERE NOT EXISTS
       (SELECT 0 FROM Query2)
)
;
------------------------------------------------------------------------------

EMPNO  FIRSTNME     WORKDEPT SEX
------ ------------ -------- ---
000060 IRVING       D11      M  
000150 BRUCE        D11      M  
000160 ELIZABETH    D11      F  
000170 MASATOSHI    D11      M  
000180 MARILYN      D11      F  
000190 JAMES        D11      M  
000200 DAVID        D11      M  
000210 WILLIAM      D11      M  
000220 JENNIFER     D11      F  

  9 record(s) selected.

tonkuma · #5 (**permalink**) 05-18-10, 13:12

Quote:

(Used "SELECT * FROM Query1" instead of simple "Query1", because of using common table expressions.)

By changing the example as following,
it will be more consistent with the general query form which I presented.

Code:

SELECT * FROM Query1
INTERSECT
(
SELECT * FROM Query2
UNION ALL
SELECT *
  FROM (SELECT * FROM Query1) q
 WHERE NOT EXISTS
       (SELECT * FROM Query2)
)

HanbingL · #6 (**permalink**) 05-18-10, 13:34

Thanks, I think this should work.

Quote:

Originally Posted by tonkuma

Maybe.....

Code:

Query1
INTERSECT
(
Query2
UNION ALL
SELECT *
  FROM (Query1) q
 WHERE NOT EXISTS(Query2)
)

Internet Services

Web Development

Digital Marketing

Consumer Tech