Joining multiple Tables
• If we need to retrieve information from multiple tables,
we join those tables. For example, if
we want to know the last names of the students who have taken section no:34566
class, we need to join Student table
and Enrolled table as follows:
SELECT Student.last
FROM Student,Enrolled
WHERE Student.ssn= Enrolled.ssn AND SecNo='34566'; --SecNo
can also be written as Enrolled.SecNo
What are Student.ssn and Enrolled.ssn? We use the table name to identify conflicting
column names: e.g., table1.column1 because ssn column exists in both
tables. If we simply say, ssn in the SELECT part, Oracle will obviously
confuse about the ssn.
(which ssn you want?)
• What is Student.ssn = Enrolled.ssn? This is a join condition not a
search or filter condition because we want to perform EQUIJOIN
operation, which return only matching rows between two tables through common
columns.
• Do we need this join condition whenever we join tables? The answer is
YES in most cases.
• What if we don’t specify this join condition?
SELECT Student.last
FROM Student,Enrolled
WHERE Enrolled.SecNo='34566';
This query will result in every possible combination of SecNo='34566' rows with with all the Student rows, which is called the result of Cartesian Product. The Cartesian Product produces every possible match of rows between two tables and can produce wrong result (spurious tuples) in most cases. Thus, Cartesian Product is rarely used as a database query.
• Each table involved in a query must be listed in the FROM clause, and each table in the FROM clause must be listed in the WHERE clause in a join condition, otherwise, the query may produce a strange result (e.g., the result of Cartesian product).
• Aliasing table names
SELECT cust_num, C.name, C.sales_num, S.name
FROM customer C, salesperson S --
C and S are alternate names for the tables
WHERE C.sales_num = S.sales_num AND credit_limit > 1000;
Using an Alias of a table is good for simplifying queries and can avoid the
naming conflicts
when joining tables.
• Joining with a temporary table
SELECT name, orderdate
FROM customer, (SELECT orderdate, amount, cust_num as order_cust_num
FROM orders WHERE amount>100)
WHERE cust_num=order_cust_num;
Note that although this way of joining tables may be convenient, this is not
a best way to join tables.
• Note JOIN is a very expensive operation in relational databases!
• Understanding how join operation works is important!