MULTIPLE TABLE QUERIES

A JOIN operation is performed when more than one table is specified in the FROM clause.  You would join two tables if you need information from both. You must specify the JOIN condition explicitly in SQL.  This includes naming the columns in common and the comparison operator.

Example 1

Find the name and courses that each faculty member teaches.
SELECT FACULTY.FACNAME, COURSENUM FROM FACULTY, CLASS WHERE FACULTY.FACID = CLASS.FACID;

FACULTY.FACNAME COURSENUM
Adams ART103A
Tanaka CIS201A
Byrne MTH101B
Smith HST205A
Byrne MTH103C
Tanaka CIS203A

When both tables have an attribute name in common, you must specify which version of the attribute that you are referring to by preceding the attribute name with the table name and a period.  (e.g., table‑name.col‑name).  This is called “qualification”.

It is sometimes more convenient to use an “alias” (an alternative name) for each table.  SQL specifies alias names in the FROM clause immediately following the actual table.  Once defined, you can use the alias anywhere in the SELECT where you would normally use the table name.

Example 2

Find the course number and the major of all students taught by the faculty member with ID number ‘F110′. (3 table JOIN)
SELECT ENROLL.COURSENUM, LNAME, MAJOR FROM CLASS , ENROLL, STUDENT WHERE FACID = ‘F110′ AND CLASS.COURSENUM = ENROLL.COURSENUM AND ENROLL.STUID = STUDENT.STUID;

ENROLL.COURSENUM

LNAME

MAJOR

MTH101B

Rivera

CIS

MTH103C

Burns

ART

MTH103C

Chin

Math

Using aliases, this would be:
SELECT E.COURSENUM, LNAME, MAJOR FROM CLASS C, ENROLL E, STUDENT S WHERE FACID = ‘F110′ AND C.COURSENUM = E.COURSENUM AND E.STUID = S.STUID;

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>