none
sql statement

    Question

  • hello guys ive i have a friend uses somekind of statement in sql he says its a join statement i dont know what are the equivalent statement in modern join???

    here are the sample

    *=

    =*

    (+) =

    thanks and god bless

    Saturday, June 22, 2013 10:07 PM

Answers

  • These are deprecated join syntax shorthand:

    *= (left outer join)

    =* (right outer join)

    (+) = (I think was Oracle flavor of specifying an outer join where the column name was followed by (+) to indicated which side was the outer) 


    Sunday, June 23, 2013 12:36 AM
  • This correct, but there is more to it than just that!

    Here is how OUTER JOINs work in ANSI/ISI Standard SQL and beyond. Assume you are given:

    Table1       Table2
     a   b        a   c
     ======       ======
     1   w        1   r
     2   x        2   s
     3   y        3   t
     4   z

    and the outer join expression: 

     Table1 
     LEFT OUTER JOIN 
     Table2
     ON Table1.a = Table2.a      <== join condition
        AND Table2.c = 't';      <== single table condition

    We call Table1 the "preserved table" and Table2 the "unpreserved table" in the query. What I am going to give you is a little different, but equivalent to the ANSI/ISO standards.

    1) We build the CROSS JOIN of the two tables. Scan each row in the result set. 

    2) If the predicate tests TRUE for that row, then you keep it. You also remove all rows derived from it from the CROSS JOIN

    3) If the predicate tests FALSE or UNKNOWN for that row, then keep the columns from the preserved table, convert all the columns from the unpreserved table to NULLs and remove the duplicates.

    So let us execute this by hand:

     Let @ = passed the first predicate
     Let * = passed the second predicate

     Table1 CROSS JOIN Table2
     a   b        a   c
     =========================
     1   w       1   r @
     1   w       2   s
     1   w       3   t *
     2   x       1   r
     2   x       2   s @
     2   x       3   t *
     3   y       1   r
     3   y       2   s
     3   y       3   t @* <== the TRUE set
     4   z       1   r
     4   z       2   s
     4   z       3   t *

     Table1 LEFT OUTER JOIN Table2
     a   b        a   c
     =========================
     3   y     3      t      <= only TRUE row
     -----------------------
     1   w     NULL   NULL   Sets of duplicates
     1   w     NULL   NULL
     1   w     NULL   NULL
     -----------------------
     2   x     NULL   NULL
     2   x     NULL   NULL
     2   x     NULL   NULL
     3   y     NULL   NULL  <== derived from the TRUE set - Remove  
     3   y     NULL   NULL
     -----------------------
     4   z     NULL   NULL
     4   z     NULL   NULL
     4   z     NULL   NULL

    the final results:

     Table1 LEFT OUTER JOIN Table2
     a   b        a   c
     =========================
     1   w     NULL   NULL
     2   x     NULL   NULL
     3   y     3      t
     4   z     NULL   NULL

    The basic rule is that every row in the preserved table is represented in the results in at least one result row. 

    There are limitations and very serious problems with the extended equality version of an outer join used in some diseased mutant products. Consider the two Chris Date tables

     Suppliers        SupParts
     sup_nbr            sup_nbr part_nbr qty
     =========        ==============
     S1               S1   P1    100
     S2               S1   P2    250
     S3               S2   P1    100
                      S2   P2    250

    and let's do an extended equality outer join like this:

     SELECT *
      FROM Supplier, SupParts
     WHERE Supplier.sup_nbr *= SupParts.sup_nbr
       AND qty < 200;

    If I do the outer first, I get:

     Suppliers LOJ SupParts
     sup_nbr sup_nbr part_nbr qty
     =======================
     S1     S1   P1    100
     S1     S1   P2    250
     S2     S2   P1    100
     S2     S2   P2    250
     S3   NULL  NULL   NULL

    Then I apply the (qty < 200) predicate and get

     Suppliers LOJ SupParts
     sup_nbr sup_nbr part_nbr qty
     ===================
     S1   S1   P1    100
     S2   S2   P1    100

    Doing it in the opposite order

     Suppliers LOJ SupParts
     sup_nbr sup_nbr part_nbr qty
     ===================
     S1   S1   P1    100
     S2   S2   P1    100
     S3   NULL NULL  NULL

    Sybase did it one way, Oracle did it the other and Centura (nee Gupta) let you pick which one -- the worst of both non-standard worlds! In ANSI/ISI Standard SQL, you have a choice and can force the order of execution. Either do the predicates after the join ...

     SELECT *
       FROM Supplier
            LEFT OUTER JOIN
            SupParts
            ON Supplier.sup_nbr = SupParts.sup_nbr
     WHERE qty < 200;

     ... or do it in the joining:

     SELECT *
      FROM Supplier
           LEFT OUTER JOIN
           SupParts
           ON Supplier.sup_nbr = SupParts.sup_nbr 
              AND qty < 200;

    Another problem is that you cannot show the same table as preserved and unpreserved in the extended equality version, but it is easy in ANSI/ISO Standard SQL. For example to find the students who have taken Math 101 and might have taken Math 102:

     SELECT C1.student, C1.math, C2.math
      FROM (SELECT * FROM Courses WHERE math = 101) AS C1
           LEFT OUTER JOIN
           (SELECT * FROM Courses WHERE math = 102) AS C2
           ON C1.student = C2.student;


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, June 23, 2013 12:52 AM

All replies

  • My understanding is that these operators were old and were discarded after SQL 2005.

    these should no longer be used.


    val it: unit=()

    Saturday, June 22, 2013 10:40 PM
  • These are deprecated join syntax shorthand:

    *= (left outer join)

    =* (right outer join)

    (+) = (I think was Oracle flavor of specifying an outer join where the column name was followed by (+) to indicated which side was the outer) 


    Sunday, June 23, 2013 12:36 AM
  • This correct, but there is more to it than just that!

    Here is how OUTER JOINs work in ANSI/ISI Standard SQL and beyond. Assume you are given:

    Table1       Table2
     a   b        a   c
     ======       ======
     1   w        1   r
     2   x        2   s
     3   y        3   t
     4   z

    and the outer join expression: 

     Table1 
     LEFT OUTER JOIN 
     Table2
     ON Table1.a = Table2.a      <== join condition
        AND Table2.c = 't';      <== single table condition

    We call Table1 the "preserved table" and Table2 the "unpreserved table" in the query. What I am going to give you is a little different, but equivalent to the ANSI/ISO standards.

    1) We build the CROSS JOIN of the two tables. Scan each row in the result set. 

    2) If the predicate tests TRUE for that row, then you keep it. You also remove all rows derived from it from the CROSS JOIN

    3) If the predicate tests FALSE or UNKNOWN for that row, then keep the columns from the preserved table, convert all the columns from the unpreserved table to NULLs and remove the duplicates.

    So let us execute this by hand:

     Let @ = passed the first predicate
     Let * = passed the second predicate

     Table1 CROSS JOIN Table2
     a   b        a   c
     =========================
     1   w       1   r @
     1   w       2   s
     1   w       3   t *
     2   x       1   r
     2   x       2   s @
     2   x       3   t *
     3   y       1   r
     3   y       2   s
     3   y       3   t @* <== the TRUE set
     4   z       1   r
     4   z       2   s
     4   z       3   t *

     Table1 LEFT OUTER JOIN Table2
     a   b        a   c
     =========================
     3   y     3      t      <= only TRUE row
     -----------------------
     1   w     NULL   NULL   Sets of duplicates
     1   w     NULL   NULL
     1   w     NULL   NULL
     -----------------------
     2   x     NULL   NULL
     2   x     NULL   NULL
     2   x     NULL   NULL
     3   y     NULL   NULL  <== derived from the TRUE set - Remove  
     3   y     NULL   NULL
     -----------------------
     4   z     NULL   NULL
     4   z     NULL   NULL
     4   z     NULL   NULL

    the final results:

     Table1 LEFT OUTER JOIN Table2
     a   b        a   c
     =========================
     1   w     NULL   NULL
     2   x     NULL   NULL
     3   y     3      t
     4   z     NULL   NULL

    The basic rule is that every row in the preserved table is represented in the results in at least one result row. 

    There are limitations and very serious problems with the extended equality version of an outer join used in some diseased mutant products. Consider the two Chris Date tables

     Suppliers        SupParts
     sup_nbr            sup_nbr part_nbr qty
     =========        ==============
     S1               S1   P1    100
     S2               S1   P2    250
     S3               S2   P1    100
                      S2   P2    250

    and let's do an extended equality outer join like this:

     SELECT *
      FROM Supplier, SupParts
     WHERE Supplier.sup_nbr *= SupParts.sup_nbr
       AND qty < 200;

    If I do the outer first, I get:

     Suppliers LOJ SupParts
     sup_nbr sup_nbr part_nbr qty
     =======================
     S1     S1   P1    100
     S1     S1   P2    250
     S2     S2   P1    100
     S2     S2   P2    250
     S3   NULL  NULL   NULL

    Then I apply the (qty < 200) predicate and get

     Suppliers LOJ SupParts
     sup_nbr sup_nbr part_nbr qty
     ===================
     S1   S1   P1    100
     S2   S2   P1    100

    Doing it in the opposite order

     Suppliers LOJ SupParts
     sup_nbr sup_nbr part_nbr qty
     ===================
     S1   S1   P1    100
     S2   S2   P1    100
     S3   NULL NULL  NULL

    Sybase did it one way, Oracle did it the other and Centura (nee Gupta) let you pick which one -- the worst of both non-standard worlds! In ANSI/ISI Standard SQL, you have a choice and can force the order of execution. Either do the predicates after the join ...

     SELECT *
       FROM Supplier
            LEFT OUTER JOIN
            SupParts
            ON Supplier.sup_nbr = SupParts.sup_nbr
     WHERE qty < 200;

     ... or do it in the joining:

     SELECT *
      FROM Supplier
           LEFT OUTER JOIN
           SupParts
           ON Supplier.sup_nbr = SupParts.sup_nbr 
              AND qty < 200;

    Another problem is that you cannot show the same table as preserved and unpreserved in the extended equality version, but it is easy in ANSI/ISO Standard SQL. For example to find the students who have taken Math 101 and might have taken Math 102:

     SELECT C1.student, C1.math, C2.math
      FROM (SELECT * FROM Courses WHERE math = 101) AS C1
           LEFT OUTER JOIN
           (SELECT * FROM Courses WHERE math = 102) AS C2
           ON C1.student = C2.student;


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, June 23, 2013 12:52 AM