1 / 66

Everything you Know About Joining Tables in Oracle is Wrong: The Case for ANSI-92 SQL

Learn how to master ANSI-92 SQL joins for improved query writing efficiency and effectiveness. Explore syntax, equi-joins, and outer joins to enhance your database skills. Join us for an educational session with expert Dan Stober from Intermountain Healthcare.

lbasso
Download Presentation

Everything you Know About Joining Tables in Oracle is Wrong: The Case for ANSI-92 SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Everything you Know About Joining Tables in Oracle is Wrong: The Case for ANSI-92 SQL Dan Stober Intermountain Healthcare Enterprise Data Warehouse Team Wednesday, September 2, 2009

  2. The pitch... • Any good salesman knows: • In order to close the sale, one must • What am I pitching? Present the features and benefits of the new product or service Overcome the objections of the customer ANSI-92 SQL!

  3. Objectives • EDUCATION: • Attendees leave session confident that they can create and understand queries written with ANSI-92 SQL joins • ADVOCACY: • Attendees leave session with desire to write ANSI-92 SQL joins in queries

  4. Dan Stober • About Me: • Data Architect • Intermountain Healthcare • Enterprise Data Warehouse (EDW) • Working in Oracle since 2001 • Started in Oracle version 8 • California State University Fresno • GO BULLDOGS! • BS in Accounting • Presentations at Oracle Conferences • Oracle Open World: 2006, 2007 • Collaborate (IOUG/OAUG): 2004, 2006, 2008 • UTOUG: 2006, 2007, 2008, 2009 • Why do I do this? • I learn something from every session I facilitate... • Either, during preparation or during the session itself

  5. How I roll... • Questions? Comments? Interrupt me! • Cell phones, Pagers: No problem! • Assumption: attendees understand traditional Oracle SQL syntax • All examples are in the slides • I am stubborn

  6. Agenda • Education: Syntax • Equi - joins (Inner joins) • ON • USING • Natural Joins • Outer joins • Pitfalls • Cartesian joins • Nuances • Advocacy • Advantages – Features and Benefits • More elegant, intuitive • Simplifies WHERE clauses • Full outer join • Accidental Cartesian products much less likely • Many of us work in environments with more than Oracle • Overcoming Objections

  7. How many have ...? • Encountered queries written in ANSI SQL? • Written queries with ANSI SQL? • Considered writing queries with ANSI? • Even know what ANSI SQL is?

  8. ANSI SQL • Microsoft Access scared me! SELECT Surveys1.Date , Surveys1.Record , Surveys1.[Survey Type] , Standard_Version.Standard_Version_Name , Chpt_Stnd_ep.Chapter_Type , Surveys1.Findings AS Observations , ObservationAction.ActionDate , ObservationAction.Action FROM ( ( ( Surveys1 INNER JOIN Standard_Version ON Surveys1.StandardVersionID = Standard_Version.ID ) INNER JOIN SurveyPeriod ON Surveys1.SurveyPeriod = SurveyPeriod.Period_ID ) INNER JOIN ( Chpt_Stnd_ep INNER JOIN ObservationAction ON Chpt_Stnd_ep.Event_ID = ObservationAction.Event_ID ) ON Surveys1.Record = Chpt_Stnd_ep.Event_ID ) INNER JOIN ( Event_Facility INNER JOIN [Hospital list] ON Event_Facility.Facility = [Hospital list].Hospital ) ON Surveys1.Record = Event_Facility.Event_ID; • In particular... • Excessive parentheses • Nesting of tables where unnecessary Don’t worry!

  9. ANSI-92 SQL • Major revision to the SQL standard • Released in 1992 • ANSI: American National Standards Institute • Join conditions included with table list • Not in WHERE clause • Outer joins more explicitly noted • Full outer joins supported • Cross-platform support • First supported by Oracle in Release 9 (2002)

  10. Basic Syntax Comparison Traditional Oracle syntax SELECT * FROM emp e , dept d WHERE e.deptno = d.deptno (+) AND sal > 1000 Whitespace is unimportant. Join condition also could be on next line ANSI-92 syntax SELECT * FROM emp e LEFT OUTER JOIN dept d ON ( e.deptno = d.deptno ) WHERE sal > 1000 Join type is explicitly specified: LEFT OUTER JOIN Keywords are NOT case sensitive.

  11. First table in list has no join condition Keyword “INNER” is optional Equi-Join SELECT * FROM emp e INNER JOIN dept d ON ( e.deptno = d.deptno ) Is the same query as SELECT * FROM emp e , dept d WHERE e.deptno = d.deptno • Matches every record from one table with every record from second table • On records where values in the joined field match • Most common join • Whitespace unimportant No commas in table list in FROM Join condition is in FROM clause with keyword “ON” Parentheses around join condition are optional

  12. Equi-Join SQL> SELECT * 2 FROM emp e 3 INNER JOIN dept d 4 ON ( e.deptno = d.deptno ) 5 / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- --------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK 14 rows selected.

  13. USING • Another way to specify the join condition • You may use it when joined column names in the two tables have the same name • If joining on more than one column, use comma USING ( fcilty_id, dept_id) • Parentheses are required here • Unlike with ON SELECT * FROM emp e INNER JOIN dept d USING ( deptno )

  14. USING SQL> SELECT * 2 FROM emp e 3 JOIN dept d 4 USING ( deptno ) 5 / DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- -------------- -------- 20 7369 SMITH CLERK 7902 17-DEC-80 800 RESEARCH DALLAS 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 SALES CHICAGO 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 SALES CHICAGO 20 7566 JONES MANAGER 7839 02-APR-81 2975 RESEARCH DALLAS 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 SALES CHICAGO 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 SALES CHICAGO 10 7782 CLARK MANAGER 7839 09-JUN-81 2450 ACCOUNTING NEW YORK 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 RESEARCH DALLAS 10 7839 KING PRESIDENT 17-NOV-81 5000 ACCOUNTING NEW YORK 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 SALES CHICAGO 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 RESEARCH DALLAS 30 7900 JAMES CLERK 7698 03-DEC-81 950 SALES CHICAGO 20 7902 FORD ANALYST 7566 03-DEC-81 3000 RESEARCH DALLAS 10 7934 MILLER CLERK 7782 23-JAN-82 1300 ACCOUNTING NEW YORK 14 rows selected. • However, there is a major difference in the results between queries with USING and those with ON...

  15. Joining With USING: DEPTNO appears in the results only once ...and DEPTNO appears before all other fields USING vs ON SQL> SELECT * 2 FROM emp e 3 JOIN dept d 4 USING ( deptno ) 5 WHERE ENAME IN ('ALLEN','SMITH','KING') 6 / DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC ------- -------- -------- --------- ---------- --------- ---------- ------- -------------- -------- 10 7839 KING PRESIDENT 17-NOV-81 5000 ACCOUNTING NEW YORK 20 7369 SMITH CLERK 7902 17-DEC-80 800 RESEARCH DALLAS 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 SALES CHICAGO 3 rows selected. Both queries use SELECT * Joining with ON: DEPTNO appears twice, once for each table ...and fields appear in the physical order from the respective tables SQL> SELECT * 2 FROM emp e 3 INNER JOIN dept d 4 ON ( e.deptno = d.deptno ) 5 WHERE ENAME IN ('ALLEN','SMITH','KING') 6 / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ------ ------- --------- -------- --------- ------- ------- ---------- ------- ------------ --------- 7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK 7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO 3 rows selected.

  16. Ambiguously defined columns 1 SELECT empno, ename, deptno, dname 2 FROM emp e 3 , dept d 4* WHERE e.deptno = d.deptno SQL> / 1 SELECT empno, ename, deptno, dname 2 FROM emp e 3 , dept d 4* WHERE e.deptno = d.deptno SQL> / SELECT empno, ename, deptno, dname * ERROR at line 1: ORA-00918: column ambiguously defined • Who among us hasn’t done this? • Why is this error even necessary? What will happen?

  17. Ambiguous Columns with USING SQL> SELECT empno, ename, deptno, dname 2 FROM emp e 3 JOIN dept d USING ( deptno ) 4 / EMPNO ENAME DEPTNO DNAME ---------- ---------- ---------- ------------ 7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 10 ACCOUNTING 7788 SCOTT 20 RESEARCH 7839 KING 10 ACCOUNTING 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 10 ACCOUNTING 14 rows selected. • No error for ambiguous column definition here • Oracle knows that values in DEPTNO column are the same • Oracle treats the DEPTNO column as a single field • How do I know? • Remember prior query comparison • And, see the next slide...

  18. USING: A new pitfall When field is included in USING, it cannot be qualified SQL> SELECT e.empno, e.ename, e.deptno, d.dname 2 FROM emp e 3 JOIN dept d USING ( deptno ) 4 / SQL> SELECT e.empno, e.ename, e.deptno, d.dname 2 FROM emp e 3 JOIN dept d USING ( deptno ) 4 / SELECT e.empno, e.ename, e.deptno, d.dname * ERROR at line 1: ORA-25154: column part of USING clause cannot have qualifier Remember: These errors apply only to queries with USING. Fields in queries with ON must be qualified just like in traditional Oracle syntax SQL> SELECT empno, ename, deptno, dname 2 FROM emp e 3 JOIN dept d USING ( deptno ) 4 WHERE e.deptno = 30 5 / WHERE e.deptno = 30 * ERROR at line 4: ORA-25154: column part of USING clause cannot have qualifier ...even when it’s used in the WHERE clase

  19. Inner join recap • Use keywords “INNER JOIN” • Or just “JOIN” by itself • “INNER” is optional • No commas in FROM clause

  20. Correlated Subquery Problem • With USING, cannot alias column • In this example, show employee with highest sal (or longest tenure) in dept SELECT deptno, dname, ename, sal FROM emp e JOIN dept d USING ( deptno ) WHERE sal = ( SELECT MAX (sal) FROM emp WHERE deptno = e.deptno ) This is definitely NOT what you should do!

  21. Correlated Subquery Problem SELECT e.deptno, dname, ename, sal FROM emp e JOIN dept d ON ( e.deptno = d.deptno ) WHERE sal = ( SELECT MAX (sal) FROM emp WHERE deptno = e.deptno) Three possible solutions: 1. Join with ON because the column alias is needed to correlate the subquery 2. Don’t correlate the subquery at all 3. Join to subquery as inline view SELECT deptno, dname, ename, sal FROM emp e JOIN dept d USING ( deptno ) WHERE ( deptno, sal ) IN ( SELECT deptno, MAX (sal) FROM emp GROUP BY deptno ) SELECT deptno, dname, ename, sal FROM emp e JOIN dept d USING ( deptno ) JOIN ( SELECT deptno, MAX (sal) sal FROM emp GROUP BY deptno ) USING ( deptno, sal )

  22. Natural Join SELECT * FROM emp NATURAL JOIN dept • Special Equi-Join • Oracle joins on ALL fields with common names between the two tables • Developer does not specify join fields • Query join condition also could have been written as NATURAL INNER JOIN descemp Name Null Type ------------- -------- ---------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) desc dept Name Null Type ------------- -------- ---------------- DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)

  23. Natural Join • Usage and pitfalls are similar to USING • Cannot use qualifier on joined fields • Joined fields appear in query results only once SQL> SELECT e.empno, e.ename, e.deptno, d.dname 2 FROM emp e nATURAL JOIN dept d; SELECT e.empno, e.ename, e.deptno, d.dname * ERROR at line 1: ORA-25155: column used in NATURAL join cannot have qualifier SQL> SELECT * FROM emp NATURAL JOIN dept 2 WHERE ROWNUM =1; DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC ------- ------- ---------- ----- ------- --------- ------- ------- ------------ ------ 20 7369 SMITH CLERK 7902 17-DEC-80 800 RESEARCH DALLAS 1 row selected.

  24. Natural Join SELECT * FROM emp NATURAL JOIN dept • Hidden Danger: • You probably do not want to join on ALL fields • Example: • last_date_updated • last_updated_by • Even if the query works now, it may not work later when someone alters the table! • Recommendation: • OK for ad-hoc queries • Do not use for production queries

  25. A Query Using Oracle Syntax Join condition is missing. This is a Cartesian product Only one record meets the WHERE clause condition What Happened?

  26. Avoiding a Cartesian product SQL> SELECT empno, ename, dname 2 FROM emp e JOIN dept d 3 WHERE d.deptno = 30 4 AND job = 'MANAGER' 5 / WHERE d.deptno = 30 * ERROR at line 3: ORA-00905: missing keyword • When query is re-written with ANSI JOIN, Oracle will not execute it if join condition(s) are not specified • Cartesian products are not impossible, though • Eg: When multiple join conditions are required and only some are missing • Fac / Dept • HR date tracked tables Partial Cartesian join SELECT first_name, last_name , primary_flag FROM per_people_fppf JOIN per_assignments_fpaf USING ( person_id ) These two tables must be joined using person_id and the effective_date fields (date tracking)

  27. Cartesian product • What is it? • Developed by René Descartes • Set theory • Every member in one set is matched with every member of a second set • Number of results: • Product of number of members from each set • Also called: Cross Product • Is it ever desirable? • Yes! • Can you do it in ANSI SQL? • Of course!

  28. CROSS JOIN SQL> SELECT COUNT(*) 2 FROM emp 3 CROSS JOIN dept 4 / COUNT(*) ---------- 56 1 row selected. OBSERVE: No join conditions were supplied • ANSI SQL syntax for an intentional Cartesian join • Unambiguous signal that the developer intended to use a Cartesian join Cartesian math

  29. Outer Joins • What is it? • Matches table A with table B • All records in table A without a match in table B • Or vice-versa The plus sign (+) goes on the side where you expect nulls SELECT * FROM emp e , dept d WHERE e.deptno (+) = d.deptno Traditional Oracle Syntax

  30. Outer Joins SELECT * FROM emp RIGHT OUTER JOIN dept USING ( deptno ) • Join using: LEFT/RIGHT OUTER JOIN • LEFT or RIGHT? • It depends upon the driving table • It will be the one without the plus sign (+) • Word OUTER is optional

  31. Left or Right? • Should point to the driving table • In this example, it is DEPT • Think of the FROM clause as one, long continuous stream • If the driving table comes first, it would be to the left • If the driving table follows, it would be to the right SELECT * FROM emp RIGHT OUTER JOIN dept USING ( deptno ) emp dept Driving table is to the right

  32. Left or Right? SELECT * FROM emp RIGHT JOIN dept USING ( deptno ) SELECT * FROM dept LEFT JOIN emp USING ( deptno ) • LEFT or RIGHT depends upon the order of the tables in the FROM clause • LEFT JOIN is much more common • Due to the natural order that tables are added to a query when chaining multiple tables • That’s a consequence of the discipline the comes from ANSI joins These two queries are equivalent

  33. Outer Joins with Filters This topic probably will cause the most confusion... • For a table participating in an OUTER JOIN, you must include any filters in the join condition Write a query to show all departments and any analysts SQL> SELECT empno, ename, job 2 , d.deptno, dname 3 FROM emp e RIGHT JOIN dept d 4 ON ( e.deptno = d.deptno ) 5 WHERE job = 'ANALYST' SQL> SELECT empno, ename, job 2 , d.deptno, dname 3 FROM emp e RIGHT JOIN dept d 4 ON ( e.deptno = d.deptno ) 5 WHERE job = 'ANALYST' 6 / EMPNO ENAME JOB DEPTNO DNAME ---------- -------- ------- ------ -------- 7902 FORD ANALYST 20 RESEARCH 7788 SCOTT ANALYST 20 RESEARCH 2 rows selected. Not the results that we wanted...

  34. Outer Joins with Filters 1 SELECT empno, ename, job 2 , d.deptno, dname 3 FROM emp e RIGHT JOIN dept d 4 ON ( e.deptno = d.deptno 5* AND job = 'ANALYST' ) SQL> / EMPNO ENAME JOB DEPTNO DNAME ---------- ---------- --------- ---------- -------------- 7788 SCOTT ANALYST 20 RESEARCH 7902 FORD ANALYST 20 RESEARCH 30 SALES 40 OPERATIONS 10 ACCOUNTING 5 rows selected. Another try... Filtering condition is included with JOIN WHY? Null Null Null • For a table participating in an OUTER JOIN, you must include any filters in the join condition

  35. Outer Joins with Filters • It is consistent with the way you handle the same situation using Oracle SQL syntax Although it seems to violate one of the primary advantages on ANSI SQL joins... • If either the two plus signs (+) are omitted, the outcome will be the same as the first ANSI example SQL> SELECT empno, ename, job 2 , d.deptno, dname 3 FROM emp e 4 , dept d 5 WHERE e.deptno (+) = d.deptno 6 AND e.job (+) = 'ANALYST' 7 / Plus Sign (+) must be included in all references to the outer joined table

  36. Full Outer Join • What is it? • Matches table A with table B • All records in table A without a match in table B • All records in table B without a match in table A

  37. Full Outer Join

  38. Full Outer Join SQL> SELECT * 2 FROM emp e 3 , dept d 4 WHERE e.deptno (+) = d.deptno (+) 5 / SQL> SELECT * 2 FROM emp e 3 , dept d 4 WHERE e.deptno (+) = d.deptno (+) 5 / WHERE e.deptno (+) = d.deptno (+) * ERROR at line 4: ORA-01468: a predicate may reference only one outer-joined table • Very tricky to do with Oracle syntax • Plus signs on both sides of the join are not permitted • So, developers are forced to resort to workarounds • ...and a lot of people get it wrong!

  39. Full Outer Join SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno (+) UNION SELECT * FROM emp e, dept d WHERE e.deptno (+) = d.deptno 1. Outer join with EMP driving • One approach frequently recommended: 2. Outer join with DEPT driving 3. UNION them together to eliminate the duplicates Records that will be selected by the TOP half of the UNION query Records that will be selected by the BOTTOM half of the UNION query Records from EMP and DEPT that match Records from EMP without a match in DEPT Records from DEPT without a match in EMP These records get selected twice, one in each half of the UNION

  40. Full Outer Join SELECT empno, ename, e.deptno, dname FROM emp e, dept d WHERE e.deptno = d.deptno (+) UNION ALL SELECT NULL, NULL, d.deptno, dname FROM dept d WHERE d.deptno NOT IN (SELECT deptno FROM emp ) • The proper way to do it with Oracle syntax • Second half selects only the records that were missed by the first half • UNION ALL in case there are any legitimate duplicates • Field names had to be enumerated in order to place NULL in the EMP fields

  41. Full Outer Join SELECT * FROM emp e FULL JOIN dept d USING ( deptno ) • Now, here it is with ANSI SQL • FULL JOIN or FULL OUTER JOIN • The word “Outer” is optional • Easy, no?

  42. Full Outer Join SQL> SELECT * 2 FROM emp e 3 FULL JOIN dept d 4 USING ( deptno ); DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC ------- ------- ---------- --------- ------- --------- ------- ------- ----------- -------- 10 7934 MILLER CLERK 7782 23-JAN-82 1300 ACCOUNTING NEW YORK 10 7839 KING PRESIDENT 17-NOV-81 5000 ACCOUNTING NEW YORK 10 7782 CLARK MANAGER 7839 09-JUN-81 2450 ACCOUNTING NEW YORK 20 7902 FORD ANALYST 7566 03-DEC-81 3000 RESEARCH DALLAS 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 RESEARCH DALLAS 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 RESEARCH DALLAS 20 7566 JONES MANAGER 7839 02-APR-81 2975 RESEARCH DALLAS 20 7369 SMITH CLERK 7902 17-DEC-80 800 RESEARCH DALLAS 30 7900 JAMES CLERK 7698 03-DEC-81 950 SALES CHICAGO 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 SALES CHICAGO 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 SALES CHICAGO 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 SALES CHICAGO 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 SALES CHICAGO 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 SALES CHICAGO 50 7777 ELLISON FOUNDER 7839 01-JUL-79 4000 40 OPERATIONS BOSTON 16 rows selected. Record from EMP table Record from DEPT table

  43. One more outer join variation • Putting together all of the things we’ve seen so far • NATURAL OUTER JOIN SQL> SELECT * 2 FROM emp 3 NATURAL RIGHT OUTER JOIN dept 4 / DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC -------- ------- ---------- --------- ------- --------- -------- ------- ----------- -------- 20 7369 SMITH CLERK 7902 17-DEC-80 800 RESEARCH DALLAS 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 SALES CHICAGO 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 SALES CHICAGO 20 7566 JONES MANAGER 7839 02-APR-81 2975 RESEARCH DALLAS 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 SALES CHICAGO 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 SALES CHICAGO 10 7782 CLARK MANAGER 7839 09-JUN-81 2450 ACCOUNTING NEW YORK 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 RESEARCH DALLAS 10 7839 KING PRESIDENT 17-NOV-81 5000 ACCOUNTING NEW YORK 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 SALES CHICAGO 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 RESEARCH DALLAS 30 7900 JAMES CLERK 7698 03-DEC-81 950 SALES CHICAGO 20 7902 FORD ANALYST 7566 03-DEC-81 3000 RESEARCH DALLAS 10 7934 MILLER CLERK 7782 23-JAN-82 1300 ACCOUNTING NEW YORK 40 OPERATIONS BOSTON 15 rows selected.

  44. Join Order table_ 3 table_ 1 PK1 table_ 2 PK2 • Permits me to: • List the tables in any order • List join conditions in any order • But, is that desirable? Oracle Join Syntax SELECT * FROM table_1 t1 , table_3 t3 , table_2 t2 WHERE t2.pk2 = t3.pk2 AND t2.pk1 = t1.pk1

  45. Join Order • Recall, in ANSI, first table should have no join condition • This is because query only “knows” about tables that have appeared in table list up to that point Query is trying to use deptno to join EMP and DEPT SQL> SELECT * 2 FROM emp USING (deptno) 3 JOIN dept 4 / SQL> SELECT * 2 FROM emp USING (deptno) 3 JOIN dept 4 / FROM emp USING (deptno) * ERROR at line 2: ORA-00933: SQL command not properly ended But, at this point, the query is unaware of DEPT...it knows only about EMP

  46. Join Order table_ 3 table_ 1 PK1 table_ 2 PK2 • Using ANSI joins, tables are less likely to be joined in a haphazard order • Enforces logic sequence in join SELECT * FROM table_1 t1 JOIN table_2 t2 USING ( pk1 ) JOIN table_3 t3 USING ( pk2 ) Once I’ve started with table_1, I cannot add table_3 until I’ve added table_2

  47. Join Order location_id locations employees department_id departments SQL> SELECT first_name, last_name, department_name, city 2 FROM hr.employees 3 JOIN hr.departments USING ( department_id) 4 JOIN hr.locations USING ( location_id ) 5 wHERErownum = 1; FIRST NAME LAST NAME DEPARTMENT_NAME CITY --------------- --------------- ------------------------------ ---------- Jennifer Whalen Administration Seattle 1 row selected. SQL> SELECT first_name, last_name, department_name, city FROM hr.employees .... NOW WHAT ? JOIN hr.locations USING ( ? ) .... I have no way to join this to hr.employees and I can’t join to hr.departments until it appears in the query... THIS FORCES ME JOIN THE TABLES IN ORDER, WHICH IS GOOD DISCIPLINE

  48. Non equi-join conditions • Join conditions do not have to be equi-joins... • You can specify other conditions with ON • Separate multiple conditions using AND SELECT * FROM emp e JOIN dept d ON ( e.deptno < d.deptno ) SELECT * FROM oryx.hsptl_admssn_col_prachacp JOIN oryx.nhqm_submission_periodnsp ON ( hacp.admit_dt BETWEEN data_begin_dt AND data_end_dt ) SELECT * FROM per_people_xppf JOIN per_assignments_fpaf ON ( ppf.person_id = paf.person_id AND ppf.hire_date BETWEEN paf.effective_start_date AND pafeffective_end_date )

  49. Syntax Recap • INNER and OUTER are always optional keywords • If words, “LEFT”, “RIGHT”, or “FULL” appear, it is assumed to be OUTER JOIN • Otherwise, it is an INNER JOIN • Default for NATURAL JOIN is INNER JOIN • ANSI SQL Join conditions can see only tables to the “left” (tables which have appeared up to this point)

  50. Objectives • EDUCATION: • Attendees leave session confident that they can create and understand queries written with ANSI-92 SQL joins • ADVOCACY: • Attendees leave session with desire to write ANSI-92 SQL joins in queries

More Related