Sunday, June 05, 2011

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>

No comments: