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= 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