Intersect

I read about this in Oracle Database 10g The Complete Reference by Kevin Loney but I tested it on an Oracle 9 database. If you join two or more select statements by intersect, only the rows  which appear in the output from every one of those statements will be displayed. The example below demonstrates this. First create a table of boys' names:

SQL> create table boys_names

  2  (boys_name varchar2(10))

  3  /
  

Table created.

SQL>
  

And also a table of girls' names:
  

SQL> create table girls_names

  2  (girls_name varchar2(10))

  3  /

Table created.

SQL> 

Add some data to the boys_names table then duplicate it:

SQL> insert into boys_names values('Tom');

1 row created.

SQL> insert into boys_names values('Dick');

1 row created.

SQL> insert into boys_names values('Harry');

1 row created.

SQL> insert into boys_names values('Kim');

1 row created.

SQL> insert into boys_names values('Alex');

1 row created.

SQL> insert into boys_names values('Sam');

1 row created.

SQL> insert into boys_names select * from boys_names;

6 rows created.

SQL> 

Do the same with the girls_names table:

SQL> insert into girls_names values('Sarah');

1 row created.

SQL> insert into girls_names values('Tina');

1 row created.

SQL> insert into girls_names values('Brenda');

1 row created.

SQL> insert into girls_names values('Kim');

1 row created.

SQL> insert into girls_names values('Alex');

1 row created.

SQL> insert into girls_names values('Sam');

1 row created.

SQL> insert into girls_names select * from girls_names;

6 rows created.

SQL> 

Display the data in both tables:

SQL> select * from boys_names

  2  /

BOYS_NAME

----------

Tom

Dick

Harry

Kim

Alex

Sam

Tom

Dick

Harry

Kim

Alex

Sam

12 rows selected.

SQL> select * from girls_names

  2  /

GIRLS_NAME

----------

Sarah

Tina

Brenda

Kim

Alex

Sam

Sarah

Tina

Brenda

Kim

Alex

Sam

12 rows selected.

SQL>

Note that 3 names appear twice in both tables. Use intersect to display these rows. Although each of these names appears twice in both tables, they are each displayed only once:

SQL> select * from boys_names

  2  intersect

  3  select * from girls_names

  4  /

BOYS_NAME

----------

Alex

Kim

Sam

SQL>

This could also be achieved as follows:

SQL> select distinct boys_name

  2  from boys_names, girls_names

  3  where boys_name = girls_name

  4  /

BOYS_NAME

----------

Alex

Kim

Sam
  

SQL>