Transcript 16c

Database Programming
Section 15 – Oracle Proprietary Join Syntax and Review
Join Commands

Two sets of commands or syntax used to make
connections between tables in a database:


Oracle proprietary joins
ANSI/ISO SQL 99 compliant standard joins
Oracle Proprietary joins


Simple join or equijoin
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1=table2.column2;
Equijoin




Simple or inner join
SELECT clause specifies columns names to retrieve
FROM clause specifies the tables to access
WHERE clause specifies how tables are to be joined
Cartesian Product Join




Joins 2 or more tables together without a join conditions
Joins every row in table 1 to every row in table 2
Number of resulting rows is M*N where M = number of
rows in table 1 and N = number of rows in table 2
You should avoid a Cartesian product by ALWAYS having
a valid WHERE clause
Restricting rows in Join


User AND operator to restrict the number of rows
returned in a join.
Qualify the column in statement with the table name or
alias
Table Alias


Simply typing lengthy statement using table alias in place
of table names.
Define table alias in the FROM clause
Nonequijoin





A non-equijoin than is a join between two tables when
column values in each table match a range of values but is
not an exact match
= operator can not be used by itself
Can use <=, >=, BETWEEN…AND
BETWEEN…AND most effective
SELECT p.code, e.cost
FROM d_packages p, d_events e
WHERE e.cost BETWEEN p.low_range AND
p.high_range;
Outer Join



Outer join is used to see rows that have a corresponding
value in another table plus those rows in one of the
tables may have missing data
Use a plus (+) after the table’s column name in the
WHERE clause of the query
Outer join cannot use the IN operator or be linked to
another condition by the OR operator
Join with plus sign






Use the plus sign to indicate the table whose column is
missing data
SELECT a.column, b.column
FROM table1 a, table2 b
with on of the following WHERE clauses
WHERE a.column(+) = b.column
WHERE a.column = b.column(+)
NEVER WHERE a.column(+) = b.column(+)
CAN’T use IN operator or OR operator in WHERE
clause