DBMS with SQL
desg
lecture_05_s12014.zipLecture_05_S12014.ppt
Welcome to
CIS 2002: DATABASE DESIGN AND IMPLEMENTATION
Semester 1, 2014 – LECTURE 5
*
*
*
USAGE OF SLIDES
Use of these lecture slides is restricted to teaching staff and students enrolled in this course. All students who use these slides should have acquired the prescribed texts.
These slides are for the personal use of students on the Study Desk only.
Students should not copy slides, allow third parties access to the slides or distribute the slides.
Copyright of these slides vests in the university and, where applicable, Cengage Education or Pearson (publishers of the textbooks)
*
*
READINGS – WEEK 5
- Study Book, modules 2, 3 and 4 (ongoing)
- Study Book, module 6 (optional – read during break)
- Study Book, module 9.3 / 9.4 (optional)
- Casteel, chapter 9
*
*
Objectives
- Identify a Cartesian join
- Create an equality join using the WHERE clause
- Create an equality join using the JOIN keyword
- Create a non-equality join using the WHERE clause
- Create a non-equality join using the JOIN…ON approach
Oracle 11g: SQL
*
Objectives (continued)
- Create a self-join using the WHERE clause
- Create a self-join using the JOIN keyword
- Distinguish an inner join from an outer join
- Create an outer join using the WHERE clause
- Create an outer join using the OUTER keyword
- Use set operators to combine the results of multiple queries
Oracle 11g: SQL
*
Purpose of Joins
- Joins are used to link tables and reconstruct data in a relational database
- Joins can be created through:
- Conditions in a WHERE clause (traditional method)
- Use of JOIN keywords in FROM clause
Oracle 11g: SQL
*
Cartesian Joins
- Created by omitting joining condition in the WHERE clause or through CROSS JOIN keywords in the FROM clause
- Results in every possible row combination (m * n)
Oracle 11g: SQL
*
Cartesian Join Example:
Omitted Condition
Oracle 11g: SQL
*
Cartesian Join Example:
CROSS JOIN Keywords
Oracle 11g: SQL
*
Equality Joins
- Link rows through equivalent data that exists in both tables
- Created by:
- Creating equivalency condition in the WHERE clause
- Using NATURAL JOIN, JOIN…USING, or JOIN…ON keywords in the FROM clause
Oracle 11g: SQL
*
Equality Joins: WHERE Clause Example
Oracle 11g: SQL
*
Qualifying Column Names
- Columns in both tables must be qualified
Oracle 11g: SQL
*
WHERE Clause Supports Join and Other Conditions
Oracle 11g: SQL
*
Joining More Than Two Tables
- Joining four tables requires three join conditions
Oracle 11g: SQL
*
Equality Joins: NATURAL JOIN
Oracle 11g: SQL
*
No Qualifiers with a NATURAL JOIN
Oracle 11g: SQL
*
Equality Joins: JOIN…USING
Oracle 11g: SQL
*
Equality Joins: JOIN…ON
- Required if column names are different
Oracle 11g: SQL
*
JOIN Keyword Overview
- Use JOIN…USING when tables have one or more columns in common
- Use JOIN…ON when same named columns are not involved or a condition is needed to specify a relationship other than equivalency (next section)
- Using the JOIN keyword frees the WHERE clause for exclusive use in restricting rows
Oracle 11g: SQL
*
Non-Equality Joins
- In WHERE clause, use any comparison operator other than the equal sign
- In FROM clause, use JOIN…ON keywords with a non-equivalent condition
Oracle 11g: SQL
*
Non-Equality Joins: WHERE Clause Example
Oracle 11g: SQL
*
Non-Equality Joins: JOIN…ON Example
Oracle 11g: SQL
*
Self-Joins
- Used to link a table to itself
- Requires the use of table aliases
- Requires the use of a column qualifier
Oracle 11g: SQL
*
Customer Table Example
Oracle 11g: SQL
*
Self-Joins: WHERE Clause Example
Oracle 11g: SQL
*
Self-Joins: JOIN…ON Example
Oracle 11g: SQL
*
Outer Joins
- Use outer joins to include rows that do not have a match in the other table
- In WHERE clause, include outer join operator (+) immediately after the column name of the table with missing rows to add NULL rows
- In FROM clause, use FULL, LEFT, or RIGHT with OUTER JOIN keywords
Oracle 11g: SQL
*
Outer Joins: WHERE Clause Example
Oracle 11g: SQL
*
Outer Joins: OUTER JOIN Keyword Example
Oracle 11g: SQL
*
Summary - JOINS
Oracle 11g: SQL
*
| Type of JOIN | Description | Traditional Method | JOIN keyword |
| Cartesian Join | Joins every row combination in both tables | No WHERE joining condition | CROSS JOIN |
| Equality Join | Joins equivalent rows that exists in both tables | WHERE joining condition with equality condition using operator ( = ) | NATURAL JOIN JOIN … USING JOIN … ON with equality condition |
| Non-Equality Join | Joins rows that satisfies non-equivalent condition in both tables | WHERE joining condition with non-equality condition using operators like ( !=, >, <, >=, <=, BETWEEN … AND) | JOIN … ON with non-equality condition |
| Self Join | Joins rows in the same table to itself with the help of table aliases and column qualifier | WHERE joining condition with same table reference | JOIN … ON with same table reference |
| Outer Join | Joins equivalent rows that exists in both tables and non-matching rows as well | WHERE joining condition with the outer join operator (+) after the column name that has missing rows in the table to display NULL rows if missing. | LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN |
Set Operators
Used to combine the results of two or more SELECT statements
Oracle 11g: SQL
*
Set Operators: UNION Example
Oracle 11g: SQL
*
Set Operators: INTERSECT Example
Oracle 11g: SQL
*
Set Operators: MINUS Example
Oracle 11g: SQL
*
