Relating multiple tables using SQL
For this handout, I am using three tables: donor, donation, drive. Donor and donation can be linked/joined based on
idno and donation and drive can be linked/joined based on driveno. There is no direct connection between donor and
drive. The rows of data and description of these three tables is shown below:BR>
SQL CODE: DONOR Table
SQL> SELECT * from donor;
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones
22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones
23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa
33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams
34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa
6 rows selected.
SQL> DESC donor;
Name Null? Type
------------------------------- -------- ----
IDNO NOT NULL VARCHAR2(5)
NAME VARCHAR2(15)
STADR VARCHAR2(15)
CITY VARCHAR2(10)
STATE VARCHAR2(2)
ZIP VARCHAR2(5)
DATEFST DATE
YRGOAL NUMBER(7,2)
CONTACT VARCHAR2(12)
SQL CODE: DONATION Table
SQL> SELECT * from donation;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20
33333 300 10-MAR-99 10
SQL> DESC donation;
Name Null? Type
------------------------------- -------- ----
IDNO VARCHAR2(5)
DRIVENO VARCHAR2(3)
CONTDATE DATE
CONTAMT NUMBER(6,2)
SQL CODE: DRIVE Table
SQL> SELECT * from drive;
DRI DRIVENAME DRIVECHAIR LASTYEAR THISYEAR
--- --------------- ------------ --------- ---------
100 Kids Shelter Ann Smith 10000 0
200 Animal Home Linda Grant 5000 0
300 Health Aid David Ross 7000 0
SQL> DESC drive;
Name Null? Type
------------------------------- -------- ----
DRIVENO VARCHAR2(3)
DRIVENAME VARCHAR2(15)
DRIVECHAIR VARCHAR2(12)
LASTYEAR NUMBER(8,2)
THISYEAR NUMBER(8,2)
SQL>
In a relational database, frequently you need to connect the data in two or more tables. The process to do this is
called a JOIN. A simple join condition joins rows in one table to appropriate rows in another table based on a common
value that exists in both tables. One of the basic ways of doing this follows the following format:
SELECT table1or2.column, table1or2.column
FROM table1, table2
WHERE table1.column = table2.column;
When you SELECT columns, you do not need to use the table name unless the column name appears in both tables, then to
avoid confusion the table name must be used. In the FROM clause, the tables that are being used are listed. In the
WHERE clause you make the connection between the tables. This is done by comparing the column that is common to both
tables looking for a match. In the example below, I am joining the driveno in the donation table to the driveno in the
drive table so I can get the name of the drive to display in my query results.
SQL CODE:
SQL> SELECT idno, donation.driveno, drivename
2 FROM donation, drive
3 WHERE donation.driveno = drive.driveno;
IDNO DRI DRIVENAME
----- --- ---------------
11111 100 Kids Shelter
23456 100 Kids Shelter
12121 200 Animal Home
33333 300 Health Aid
If the where clause is omitted, then all possible connections are made (all rows in the first table are joined to all
rows in the second table) and the results are meaningless as illustrated in the example below. This is called a
Cartesian product and can be avoided by including a valid join condition.
SQL CODE:
SQL> SELECT idno, donation.driveno, drive.driveno, drivename
2 FROM donation, drive;
IDNO DRI DRI DRIVENAME
----- --- --- ---------------
11111 100 100 Kids Shelter
12121 200 100 Kids Shelter
23456 100 100 Kids Shelter
33333 300 100 Kids Shelter
11111 100 200 Animal Home
12121 200 200 Animal Home
23456 100 200 Animal Home
33333 300 200 Animal Home
11111 100 300 Health Aid
12121 200 300 Health Aid
23456 100 300 Health Aid
33333 300 300 Health Aid
12 rows selected.
When joining tables, you can make up table aliases so that instead of having to refer to the entire table by name, you
can used the table alias. The table aliases can be as short as one character. In the example below, I gave the
donation table an alias of do and the drive table an alias of dr. I then used these aliases in the select statement
and in the where statement that joined the tables. The rule for aliases is that if they are used in the FROM clause,
they must be used throughout the SELECT.
SQL CODE:
SQL> SELECT idno, do.driveno, dr.driveno, drivename
2 FROM donation do, drive dr
3 WHERE do.driveno = dr.driveno;
IDNO DRI DRI DRIVENAME
----- --- --- ---------------
11111 100 100 Kids Shelter
23456 100 100 Kids Shelter
12121 200 200 Animal Home
33333 300 300 Health Aid
SQL>
There can be additional conditions included with the join condition that limit the range of the selection. In the
example below, the WHERE clause joins the two tables and also limits the drives that are selected with the clause
do.driveno > '100'.
SQL CODE:
SQL> SELECT idno, do.driveno, dr.driveno, drivename
2 FROM donation do, drive dr
3 WHERE do.driveno = dr.driveno AND do.driveno > '100';
IDNO DRI DRI DRIVENAME
----- --- --- ---------------
12121 200 200 Animal Home
33333 300 300 Health Aid
SQL>
In the example below, I am joining three tables. Note that at this stage, all of the tables are in a one-to-one
relationship through the idno which will join the donor table to the donation table and through the driveno which will
join the donation table to the drive table. As soon as a donor makes a second contribution, the one-to-one relationship
based on simply idno will no longer exist. In this example I have put table names in front of idno and driveno because
these names exist in more than one table. The other column names stand alone because they occur only in one table,
therefore confusion about which name is not a problem. In the from clause I have listed all three tables involved. In
the where clause I have joined the donor table to the donation table and then joined the donation table to the drive
table.
SQL CODE:
1 SELECT donor.idno, name, donation.driveno, drivename, contamt
2 FROM donor, donation, drive
3* WHERE donor.idno = donation.idno and donation.driveno = drive.driveno
SQL> /
IDNO NAME DRI DRIVENAME CONTAMT
----- --------------- --- --------------- ---------
11111 Stephen Daniels 100 Kids Shelter 25
23456 Susan Ash 100 Kids Shelter 20
12121 Jennifer Ames 200 Animal Home 40
33333 Nancy Taylor 300 Health Aid 10
JOIN Explanation:
The joins that we are going to look at are equijoins, non-equijoins, outer joins and self joins.
Equijoin
An equijoin is the kind that has been illustrated so far in this handout. Other names for equijoins are simple joins
or inner joins. An equijoin means that values in the tables being compared must be equal.
Non-equijoin
A non-equijoin means that there is not a direct join between two tables. The relationship might be that you relate a
number on one table to a range on another. For example one table might have a student's grade and the other might have
a range of 1 thru 6 for elementary, 7 thru 8 for junior high etc. You could then compare the student's grade to the low
and high for each grade in the range and determine the group the student is in.
SELECT grade.name, grade.gradelevel, schools.groupname
FROM grade, schools
WHERE grade.gradelevel BETWEEN schools.lowgrade AND schools.highgrade;
Outer join
An outer join can be used to display records that do not satisfy the join condition. To do this, we put the join
operator which is a plus sign in parenthesis in the where clause on the side of the join that is missing the information.
The result is the creation of null rows on the side that is missing the information which are joined with the side that
contains information you need to see. For this example, I added a record to the drive table that contained a drive that
did not exist in the donation table because no one had given to that drive.
SQL CODE:
SQL> select * from drive;
DRI DRIVENAME DRIVECHAIR LASTYEAR THISYEAR
--- --------------- ------------ --------- ---------
100 Kids Shelter Ann Smith 10000 0
200 Animal Home Linda Grant 5000 0
300 Health Aid David Ross 7000 0
SQL> INSERT INTO drive
2 values('400', 'Half Way', 'Robert Doe', 0,0);
1 row created.
SQL> SELECT * from drive;
DRI DRIVENAME DRIVECHAIR LASTYEAR THISYEAR
--- --------------- ------------ --------- ---------
100 Kids Shelter Ann Smith 10000 0
200 Animal Home Linda Grant 5000 0
300 Health Aid David Ross 7000 0
400 Half Way Robert Doe 0 0
SQL>
The code below illustrates the outer join. There is now a drive 400, but there have been no contributions to the drive.
Therefore the donation side is missing the data while the drive side has the data. Therefore in setting up the test,
the (+) is put on the donation side. The second example below eliminates the (+) on the donation side and therefore the
drive 400 does not display.
SQL CODE:
SQL> SELECT do.driveno, do.contamt, dr.driveno, dr.drivename
2 FROM donation do, drive dr
3 WHERE do.driveno(+) = dr.driveno;
DRI CONTAMT DRI DRIVENAME
--- --------- --- ---------------
100 25 100 Kids Shelter
100 20 100 Kids Shelter
200 40 200 Animal Home
300 10 300 Health Aid
400 Half Way
SQL> SELECT do.driveno, do.contamt, dr.driveno, dr.drivename
2 FROM donation do, drive dr
3 WHERE do.driveno = dr.driveno;
DRI CONTAMT DRI DRIVENAME
--- --------- --- ---------------
100 25 100 Kids Shelter
100 20 100 Kids Shelter
200 40 200 Animal Home
300 10 300 Health Aid
SQL>
Self join
A self join is used to join a table to itself. For example, let's say you have an inventory file that contains jackets
and skirts that can be sold separately or together as a suit. You will carry the coordinate in a field called pairset.
SQL CODE:
SQL> SELECT* FROM inventry;
ITE ITEMNAME PAI
--- ---------- ---
111 jacket 333
222 jacket 555
333 skirt 111
444 blouse
555 shirt 222
SQL> DESC inventry
Name Null? Type
------------------------------- -------- ----
ITEMNO VARCHAR2(3)
ITEMNAME VARCHAR2(10)
PAIRSET VARCHAR2(3)
SQL> list
1 SELECT fst.itemno, fst.itemname, snd.itemname
2 FROM inventry fst, inventry snd
3* WHERE fst.itemno = snd.pairset
SQL> /
ITE ITEMNAME ITEMNAME
--- ---------- ----------
111 jacket skirt
222 jacket shirt
333 skirt jacket
555 shirt jacket