JOINING TABLES


Tables can be joined such that results from one or more tables appear in the same query result. Joins can be an outer join, a Cartesian Product (a cross join), a table joined to itself (a self join), or a semi join.

  • Oracle join syntax is placed into the WHERE clause using the (+) operator (outer join operator), determining the side of an outer join deficient in information:

     SELECT * FROM <table> [WHERE <column> <comparison> <column> [(+)]]     |[WHERE <column>[(+)] <comparison> <column>] AND [NOT]| OR [NOT] ... ][GROUP BY][ORDER BY]; 

  • ANSI join syntax is placed into the FROM clause using the JOIN keyword:

     SELECT * FROM <table> [JOIN <table> [JOIN <table> ...]] [WHERE][GROUP BY][ORDER BY]; 

  • ANSI join syntax allows the CROSS JOIN clause to create a Cartesian Product between two tables:

     SELECT * FROM <table> [[CROSS] JOIN <table> [[CROSS] JOIN <table> ...]] [WHERE][GROUP BY][ORDER BY]; 

  • ANSI join syntax can use the NATURAL keyword to create both inner and outer joins, joining tables on columns with the same name in the different tables:

     SELECT * FROM <table> [[NATURAL] JOIN <table> [[NATURAL] JOIN <table> ...]] [WHERE][GROUP BY][ORDER BY]; 

  • ANSI join syntax can utilize the USING clause to specify exact column names to join on, avoiding joins on same-named columns with different meanings:

     SELECT * FROM <table> [JOIN <table> USING(<column>[,...])     [JOIN <table> ...] USING(<column>[,...])] [WHERE][GROUP BY][ORDER BY]; 

  • ANSI join syntax can utilize the ON clause to specify exact column names to join on, specifically when different-named columns are required in the join, usually requiring a table or alias reference:

     SELECT * FROM <table> a [JOIN <table> b     ON (a.<column>=b.<column> [AND [NOT]| OR [NOT] ... ] [,...])         [JOIN <table> c] ON(...)] [WHERE][GROUP BY][ORDER BY]; 



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net