WORKING WITH MULTIPLE TABLES – JOIN OPERATIONS
Author: Dr. Vijay V. Raghavan
JOIN NAME |
EXAMPLE |
EXPLANATION |
OLD-STYLE JOIN |
SELECT patient.* charge.* FROM patient, charge; | This will simply result in a table that has every row of the second table (charge) for each row of the first table(patient). The result is the Cartesian product (cross product) of the two tables. The resulting table is virtually meaningless. But, it is easy to think of this join as the first step that SQL internally takes in all joins. |
| SELECT patient. *, Charge. * FROM patient, charge WHERE patient.pat_id = charge.pat_id; | This gives a more meaningful result –when the where clause is added. | |
| Can use CORRELATION NAMES | ||
CROSS JOIN |
SELECT * FROM patient CROSS JOIN charge; | Equivalent to old-style join before the WHERE clause. |
NATURAL JOIN |
SELECT * FROM patient NATURAL JOIN charge; | Also known as natural equi-join because it selects rows from the two tables that have matching values for columns that share the same name. |
COLUMN-NAME JOIN |
SELECT * FROM patient JOIN charge USING(pat_id); | Natural joins use all columns from the two tables that share the same name. We can restrict the columns to be considered in finding matching values using the column-name join. |
OUTER JOIN |
All the joins discussed above are inner joins. A general characteristic of inner joins is to find matching values and find the rows that have these matching values. Outer joins retain unmatched rows from one or both the tables, depending on the keyword – LEFT, RIGHT or FULL – used. | |
| LEFT OUTER JOIN SELECT patient_id, patient_last_name, charge_date FROM patient LEFT OUTER JOIN charge; |
This will preserve in the result set those patients who have NOT charged, although the charges for them will have null values. Note that we still need to have matching column names. | |
UNION |
SELECT student_id FROM class1 UNION SELECT student_id FROM class2; | Will result in students enrolled in class1 and class2; if the same student is enrolled in both classes UNION will eliminate the duplicate of that student. If UNION ALL is used the duplicates will be preserved. |
Please send your comments to: Raghavan
© Dr. Vijay V. Raghavan
BACK TO Table of contents
BACK TO DATABASE Main Page