Translating sql syntax from LEFT OUTER to *=

Translating sql syntax from LEFT OUTER to *=

Post by Abdu » Tue, 14 Jul 1998 04:00:00



It seems that translating a sql syntax like:
    LEFT OUTER
    JOIN table1 ON (table1.cust_id    = table2.usage_id)
    LEFT OUTER
    JOIN table3     ON (table3.phoneNum     =  table4.phoneNum)

to

table1.cust_id *= table2_usage_id and
table3.PhoneNum *= table4.PhoneNum

are not equivalent because in the second case I keep getting '..inner
member...'

Does this mean the first syntac allows me more than one join and the
second allows only one?
Basically how do you translate from the first syntax to the second and
is there any advantage from using the first?

Abdu

 
 
 

Translating sql syntax from LEFT OUTER to *=

Post by Abdu » Tue, 14 Jul 1998 04:00:00


It seems that translating a sql syntax like:
    LEFT OUTER
    JOIN table1 ON (table1.cust_id    = table2.usage_id)
    LEFT OUTER
    JOIN table3     ON (table3.phoneNum     =  table4.phoneNum)

to

table1.cust_id *= table2_usage_id and
table3.PhoneNum *= table4.PhoneNum

are not equivalent because in the second case I keep getting '..inner
member...'

Does this mean the first syntac allows me more than one join and the
second allows only one?
Basically how do you translate from the first syntax to the second and
is there any advantage from using the first?

Abdu

 
 
 

Translating sql syntax from LEFT OUTER to *=

Post by Roy Harv » Wed, 15 Jul 1998 04:00:00


Abdu,

Quote:>It seems that translating a sql syntax like:
>    LEFT OUTER
>    JOIN table1 ON (table1.cust_id    = table2.usage_id)
>    LEFT OUTER
>    JOIN table3     ON (table3.phoneNum     =  table4.phoneNum)

So where did table4 come from?  And table2 also, for that matter.
They are not shown except in the ON test.

Quote:>to

>table1.cust_id *= table2_usage_id and
>table3.PhoneNum *= table4.PhoneNum

Do you really want to join table1 to table2, and table3 to table4,
without doing any sort of join between the table1/2 set and the table
3/4 set?

Quote:>are not equivalent because in the second case I keep getting '..inner
>member...'
>Does this mean the first syntac allows me more than one join and the
>second allows only one?

Yes.  The old *= syntax did not allow nesting of outer joins.  This
was corrected when the SQL-92 join syntax was introduced, which moved
the join criteria to the FROM clause.

Quote:>Basically how do you translate from the first syntax to the second and
>is there any advantage from using the first?

You cannot always translate from the LEFT OUTER JOIN syntax to the *=
syntax because the *= approach has severe limitation.  Standardize on
the LEFT OUTER JOIN syntax, it is the new standard and avoids the
problems and limitations of *=.

Roy

 
 
 

1. LEFT OUTER LOOP JOIN and LEFT OUTER JOIN

hi all:
        for a long time, I 've been confused about how to use LEFT OUTER
LOOP JOIN and LEFT OUTER JOIN,
sometimes, when I construct a query with LEFT OUTER JOIN, it's faster than
LEFT OUTER LOOP JOIN, but there is other time,
if I construct a query with LEFT OUTER LOOP JOIN, it's faster than LEFT
OUTER JOIN,  this is a mystery to me,  does anyone know
how to use these JOIN TYPE properly?  like giving some explaination or some
examples?

                                               please help!
                                                            thanks
                                                                        -Jay

2. SQL "Inset into"Times out periodically - HELP

3. v8.1.5: LEFT OUTER JOINs syntax

4. dot4 printer support

5. left outer join syntax

6. I want Using "Tool Tip Text" in fpw2.6a

7. Left Outer Join Syntax

8. LEFT JOIN and LEFT OUTER JOIN

9. Left-Outer join and Right-Outer join

10. Left outer join works, Right outer join just goes on for ever, processor at 100%

11. LEFT OUTER JOIN vs RIGHT OUTER JION