Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71

    Question Unanswered: Different Between = and *=

    Hello All.

    Question 1: Any idea what's the different between = and *= in the below SQL statements?


    (A) Uses "=" in the where condition

    update cust_dely_reliabity_2
    set bg_txt = bg.product_hierarchy_text,
    bu_txt = bu.product_hierarchy_text,
    mag_txt = mag.product_hierarchy_text,
    ag_txt = ag.product_hierarchy_text
    from cust_dely_reliabity_2 t1,
    t179t bg,
    t179t bu,
    t179t mag,
    t179t ag
    where substring(t1.prod_hier,1,4) = bg.product_hierarchy
    and substring(t1.prod_hier,1,8) = bu.product_hierarchy
    and substring(t1.prod_hier,1,11) = mag.product_hierarchy
    and t1.prod_hier = ag.product_hierarchy



    (B) Uses "*=" in the where condition.

    update cust_dely_reliabity_2
    set bg_txt = bg.product_hierarchy_text,
    bu_txt = bu.product_hierarchy_text,
    mag_txt = mag.product_hierarchy_text,
    ag_txt = ag.product_hierarchy_text
    from cust_dely_reliabity_2 t1,
    t179t bg,
    t179t bu,
    t179t mag,
    t179t ag
    where substring(t1.prod_hier,1,4) *= bg.product_hierarchy
    and substring(t1.prod_hier,1,8) *= bu.product_hierarchy
    and substring(t1.prod_hier,1,11) *= mag.product_hierarchy
    and t1.prod_hier *= ag.product_hierarchy


    Question 2: Will there any different in terms of processing time?


    Any advise is very much appreciated. Thank you.


    Best regards
    Teck Boon

  2. #2
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: Different Between = and *=

    = is the equivalent for INNER JOIN
    *= is the equivalent for LEFT OUTTER JOIN
    =* is the equivalent for RIGHT OUTTER JOIN

    Now to understand the difference between INNER JOIN and OUTTER JOIN (LEFT, RIGHT or FULL), consider that you have the folowing two tables in your Database:

    Table A
    =====
    ID Name
    --------------
    1 AAA
    2 BBB
    3 CCC


    Table B
    =====
    PK VAL FK_ID
    -------------------
    100 1.2 1
    200 1.3 3
    210 5.4 1


    The relation between the two tables above is set by ID->FK_ID couple.


    1) INNER JOIN:

    The result of:

    select ID,Name,PK,VAL from A inner join B on A.ID=B.FK_ID

    or

    select ID,Name,PK,VAL from A,B where A.ID = B.FK_ID

    will be:

    ID Name PK VAL
    ==================
    1 AAA 100 1.2
    1 AAA 210 5.4
    2 BBB 200 1.3


    2) LEFT OUTTER JOIN:

    The result of:

    select ID,Name,PK,VAL from A left outter join B on A.ID=B.FK_ID

    or

    select ID,Name,PK,VAL from A,B where A.ID *= B.FK_ID

    will be:

    ID Name PK VAL
    ==================
    1 AAA 100 1.2
    1 AAA 210 5.4
    2 BBB 200 1.3
    3 CCC Null Null


    =,*=,=* are used in WHERE clause to set a relation between two tables (this is the SQL'92 standard)

    INNER JOIN (or simply JOIN); LEFT OUTTER JOIN (or simply LEFT JOIN); RIGHT OUTTER JOIN (or shor RIGHT JOIN) are used in FROM clause of a SQL statement and they are now supported by almost every DBMS on the market.


    For more description of INNER and OUTTER joins see SQl Server Books on line (BOL).

    ionut calin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •