Opposite of INTERSECT in Oracle

Question

I have two selects and I want to combine them in such a way, that only rows unique in both selects are returned. Is there any built-in way in Oracle 10g to achieve this?

I know I can do something like this:

(select1 UNION select2)
MINUS
(select1 INTERSECT select2)

but I would like to avoid it. Both select1 and select2 have 20 lines, so this way would be really obscure and difficult to maintain.

Answer 1

In Oracle 10g, you've got Common Table Expressions at your disposal.

WITH
  select_1 AS (
    SELECT *
    FROM your_table
    WHERE your_condition = 1
  ),
  select_2 AS (
    SELECT *
    FROM your_other_table
    WHERE your_other_condition = 1
  )
SELECT * FROM select_1
UNION
SELECT * FROM select_2
MINUS
(
  SELECT * FROM select_1
  INTERSECT
  SELECT * FROM select_2
);

This keeps your subqueries maintainable and the purpose of your final query clear.

Of course, having Oracle add a SYM_DIFFERENCE operator to SQL would be even better, but I'm not holding my breath — they're still not convinced a BOOLEAN datatype would be a good idea.

Answer 2

Here's another idea:

• Do a full outer join of select1 and select2
• Use only those records with select1.id = NULL (record is only in select2) or select2.ID = NULL (record is only in select1)

like this:

SELECT *
FROM select1 FULL OUTER JOIN select2 on select1.id = select2.id
WHERE select1.id is null or select2.id is null