I am maintaining some ISQL that was created by someone else. In the
where clause of the ISQL there are statements containing "=", "=*",
and "*=". Can someone explain the differences.

TIA,

Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP

Lawrence,

The "=" conditions will return a row only if a match is found in
both of the tables in the condition.

The "=*" condition will return a row for every qualifying row in
the right-hand table and will, optionally, return a matching row
from the left-hand table. If a qualifying row is not found in the
left-hand table, any values coming from that table's row are set
to null.

The "*=" condition works in the opposite way. Qualifying rows in
the right-hand table are always returned. If a qualifying row is
not found in the right-hand table, any values coming from that
table's row are set to null.

The "=*" and "*=" conditions are called "right outer join" and
"left outer join", respectively. The "=" condition is called
an "inner join".

Hope this helps.

        Geoff (DBA, Esq., ...)

             304 Vassar Street, Cambridge, MA 02139 USA
======================================================================

All Three are used in a 'where' clause to define the type of equate
that is to take place.  The '=' means that in order for a match
between two tables to be considered satisfied, there must be a row in
each table that satisfies the equate statement.  When the '*=' or '=*'
is used, all rows on the '=' side will be returned regardless of
whether or not there is a match on the other side.  The last two are
called 'outer-joins'.

For example:

table1 contains:

        field1          field2
        1               a
        1               b
        1               c
        2               w
        2               x

table2 contains

        field3          field4
        a               1a
        b               2b
        e               4e

the following select:

        select * from table1 a, table2 b where a.field2 = b.field3

would return:

        field1          field2          field3          field4
        1               a               a               1a
        1               b               b               2b

but the following select:

        select * from table1 a, table2 b where a.field2 =* b.field3

would return:

        field1          field2          field3          field4
        1               a               a               1a
        1               b               b               2b
        1               c               null            null
        2               w               null            null
        2               x               null            null

but the following select:

        select * from table1 a, table2 b where a.field2 *= b.field3

would return:

        field1          field2          field3          field4
        1               a               a               1a
        1               b               b               2b
        null            null            e               4e

It can get more complicated depending on what else is in your where
clause and if other tables are included in 'outer join'(s) too.  You
should see your SQL ref manual for additonal info.  Hope this helps.

Used during outer joins, "=" returns only the result set that matches,
*= includes in the results all the rows from the first table, not just
the ones where the joined columns match, "=*" includes all results from
second table, etc.

/greg

writes:

Mark