Guide to Oracle SQL (Buffalo State University Version)
Displaying Data from Multiple Tables
Almost any "real world" database will have more than one (1) table. In fact, a single database may have ten (10) or more tables. For example, a school course database might have a facultyTable, studentTable, courseTable, gradeTable, and roomTable. The facultyTable and studentTable data may store some of the data in other tables such as the addressTable, emailTable, phoneTable, etc. This type information collection is a relational Database. Each table has a primary key and one (1) or more foreign keys.
Joining Multiple Tables
To retreive information from multiple tables you need to join tables using the secondary key from one table to relate to the primary key from another table. Tables can be "joined" without using a JOIN clause. This is referred to as a NATURAL JOIN SQL will determine if the two (2) tables have a field in common and, if so, use that field to produce the desired result set. However, as the next figure illustrates, this results in what is now as a Cartesian JOIN. The result set has thirty-six (36) rows because the facultyTable and the officeTable each hav six (6), so 6 X 6 = 36. Not usally what you would like to see in your result set.
Type of Join
SQL uses a common field to produce a Cartesian Join. Not recommended.
This is also know as an INNER JOIN. Returns all the records from the table using the ON clause from the left side of the equals sign (=) and only those records from the table on the right side thatmatch the specified condition.
Returns all the records from the table on the right side of the equals sign (=) and only those records from the table on the left side that match the specified condition.
SQL creates a temporary fopy of a table and then returns all the records from the real table and the temporary table that match the specified condition.
Figure 7-2: Type of SQL Joins
The JOIN Clause
To retreive information from multiple tables you need to join tables using the secondary key from one table to relate to the primary key from another table.
The Self Join
There are times when the result you are looking for requires that you join one table with a copy of itself. For example, employees and their manager. The manager *is* an employee so to get the manager's name you need to reterive it from the employeeTable. This is what is known as a SELF JOIN. You need create a temporary copy of the employeeTable and use a table alias to refer to that temporary table in the fields of your SELECT statement.
Displaying Data That Does Not Meet a Condition
There are times when the result you are looking for requires that the data you need to reterive does not meet a certain condition. For example, customers who have never placed an order.