Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Mohammad.

Asked: August 15, 2017 - 6:05 pm UTC

Last updated: September 27, 2022 - 3:24 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

hi TOM,
please tell me, what is the opposite for INTERSECT?

and Connor said...

Intersect gives you the rows that are common to two result, eg

SQL> create table t1 ( x int );

Table created.

SQL> create table t2 ( x int );

Table created.

SQL>
SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (2);

1 row created.

SQL> insert into t1 values (3);

1 row created.

SQL> insert into t1 values (4);

1 row created.

SQL> insert into t1 values (5);

1 row created.

SQL>
SQL> insert into t2 values (3);

1 row created.

SQL> insert into t2 values (4);

1 row created.

SQL> insert into t2 values (5);

1 row created.

SQL> insert into t2 values (6);

1 row created.

SQL> insert into t2 values (7);

1 row created.

SQL>
SQL> select * from t1 intersect select * from t2;

         X
----------
         3
         4
         5

3 rows selected.


So depending on how you want to interpret the statement "all the rows that are NOT common" you could do something like the following:

SQL>
SQL>
SQL> ( select * from t1 minus select * from t2 )
  2  union all
  3  ( select * from t2 minus select * from t1 );

         X
----------
         1
         2
         6
         7

4 rows selected.

SQL>
SQL>
SQL> select * from t1 full outer join t2 on t1.x = t2.x
  2  where t1.x is null or t2.x is null;

         X          X
---------- ----------
                    6
                    7
         1
         2

4 rows selected.


or if you think about the classic Venn diagram of intersect, you could write:

SQL> select * from t1
  2  union all
  3  select * from t2
  4  minus
  5  ( select * from t1 intersect select * from t2 );

         X
----------
         1
         2
         6
         7

4 rows selected.


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

Mohammad Ammourah, August 16, 2017 - 7:29 pm UTC

Thank you for your quick and useful answer.

but sorry my question was not clear,
what i need to know is, how to build one sql query to return all values in table t1, and all values in table t2 that are not exists in table t1

is there a simple way to do that, other than using sub-query?
select t1.x from t1
union
select t2.x from t2 where t2.x not exists(select 1 from t1 where t1.x = t2.x)
Connor McDonald
August 17, 2017 - 1:25 am UTC

Plenty of options

SQL> create table t1 ( x int );

Table created.

SQL>
SQL> create table t2 ( x int );

Table created.

SQL>
SQL>
SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (2);

1 row created.

SQL> insert into t1 values (3);

1 row created.

SQL> insert into t1 values (4);

1 row created.

SQL> insert into t1 values (5);

1 row created.

SQL> insert into t2 values (3);

1 row created.

SQL> insert into t2 values (4);

1 row created.

SQL> insert into t2 values (5);

1 row created.

SQL> insert into t2 values (6);

1 row created.

SQL> insert into t2 values (7);

1 row created.

SQL>
SQL> select x from t1
  2  union
  3  select x from t2;

         X
----------
         1
         2
         3
         4
         5
         6
         7

7 rows selected.

SQL>
SQL> select * from t1 full outer join t2
  2  on ( t1.x = t2.x)
  3  where t1.x is not null or
  4   ( t1.x is null and t2.x is not null );

         X          X
---------- ----------
         1
         1
         1
         2
         3          3
         4          4
         5          5
                    6
                    7

9 rows selected.

SQL>
SQL> select * from t1
  2  union all
  3  select * from t2 where x not in ( select x from t1 ) ;

         X
----------
         1
         1
         1
         2
         3
         4
         5
         6
         7

9 rows selected.

SQL>


Thank you

Mohammad Ammourah, August 17, 2017 - 6:05 am UTC

Thank you very much

Documentation 21c

Asim, September 14, 2022 - 5:17 pm UTC

Syntax diagram here in 21c documentation here

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__I2126435

only Shows UNION, UNION ALL, INTERSECT, MINUS, its not showing , MINUS ALL, EXCEPT , EXCEPT ALL, INTERSECT ALL

Whereas these set operators are show in new features





Chris Saxon
September 27, 2022 - 3:24 pm UTC

I've raised this with the doc team.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.