Database Programming

1. Database Programming Section 15 – Oracle Proprietary Join Syntax and Review

2. 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

3. Oracle Proprietary Joins • Cartesian Product • Equijoin • Non-equijoin • Outer join • Self join

4. Oracle Proprietary Joins • Oracle proprietary syntax uses a join condition in the WHERE clause • SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;

5. Joining multiple tables • To join n tables together, • you need a minimum of n-1 join conditions.

6. Oracle Proprietary joins • Simple join or equijoin • SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column1=table2.column2;

7. ANSI Standard Joins • Cross joins • Natural joins • Using clause • Join on • Left and Right outer joins • Full (two sided) outer joins • Arbitrary join conditions for outer joins

8. 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 • An equijoin is a table join that combines rows that have the same values for the specified columns • Example on next slide

9. Equijoin

10. Retrieving Records with Equijoins • SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id;

11. Equijoin • SELECT d_play_list_items.song_id, d_play_list_items.event_id, d_track_listings.cd_numberFROM d_play_list_items, d_track_listingsWHERE d_play_list_items.song_id = d_track_listings.song_id;

12. 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

13. Cartesian Product Join Example • EMPLOYEES (20 rows) • SELECT employee_id, last_name, depatment_idFROM employees; • DEPARTMENTS (8 rows) • SELECT department_id, department_name, location_idFROM departments;

14. Restricting rows in Join • The Where clause can be used to restrict the rows considered in one or more tables of the 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 • SELECT d_play_list_items.song_id, d_play_list_items.event_id, d_track_listings.cd_number FROM d_play_list_items, d_track_listings WHERE d_play_list_items.song_id = d_track_listings.song_id AND d_play_list_items.event_id < 105;

15. Using the AND operator • SELECT last_name, employees.department_id, department_name FROM employees, departments WHERE employees.department_id = departments.department_id AND last_name = 'Matos';

16. Table Alias • Simply typing lengthy statement using table alias in place of table names. • Define table alias in the FROM clause • SELECT d_track_listings.song_id AS TRACK, d_play_list_items.song_id AS " PLAY LIST" FROM d_play_list_items, d_track_listings WHERE d_play_list_items.song_id = d_track_listings.song_id;

17. Table Aliases • Another way to make statements easier to read is to use table aliases. A table alias is similar to a column alias; it renames an object within a statement. It is created by entering the new name for the table just after the table name in the from-clause. However, if a table alias is used in the FROM clause, then that table alias must be substituted for the table name throughout the SELECT statement. • SELECT p.song_id , t.song_id FROM d_play_list_items p, d_track_listings t WHERE p.song_id = t.song_id;

18. 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 • Retrieve data from a table that has no corresponding column in another table. • = operator can not be used by itself • Can use <=, >=, BETWEEN…AND • BETWEEN…AND most effective • SELECT p.code, e.costFROM d_packages p, d_events eWHERE e.cost BETWEEN p.low_range AND p.high_range;

19. 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

20. Example • SELECT d.department_id, e.last_name FROM employees e, departments d WHERE e.department_id = d.department_id (+);

21. Join with a Plus sign (cont.) • SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column; • SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column(+); • SELECT table1.column, table2.column FROM table1, table2 NEVER table1.column(+) = table2.column(+);

22. Join with plus sign • Use the plus sign to indicate the table whose column is missing data • SELECT a.column, b.columnFROM table1 a, table2 bwith 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