What is difference between "=", "=*", "*=" in where clause 
Author Message
 What is difference between "=", "=*", "*=" in where clause

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





Tue, 19 Jan 1999 03:00:00 GMT
 What is difference between "=", "=*", "*=" in where clause

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., ...)


Quote:

> 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




--
======================================================================
              "Mister!" He said with a sawdusty sneeze,
              "I am the Lorax. I speak for the trees."
                             - Dr. Seuss


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



Tue, 19 Jan 1999 03:00:00 GMT
 What is difference between "=", "=*", "*=" in where clause

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.



Fri, 22 Jan 1999 03:00:00 GMT
 What is difference between "=", "=*", "*=" in where clause

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


Quote:

> 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




--
--------------------------------------------------------------------
Greg Roody                                         "Call me Dilbert"
      dazed, confused & overwhelmed....  pretty much normal


--------------------------------------------------------------------


Fri, 22 Jan 1999 03:00:00 GMT
 What is difference between "=", "=*", "*=" in where clause



Quote:
>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.

The *= and =* are outer joins. See for example, Sybase System 10 SQL Server
Reference Manual volume 1 page 3-81.

Quote:
>Lawrence M. Seldin, CMC, CPC

Paul Rambags


Fri, 22 Jan 1999 03:00:00 GMT
 What is difference between "=", "=*", "*=" in where clause


writes:

Quote:

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

Are you sure about this? Wouldn't it be the other way round?

Mark



Sun, 24 Jan 1999 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. max of ("...","...","..")

2. How to make a "decimal"-field to an "integer"-field

3. "."and ","

4. "."and ","

5. The ""string""

6. aReport."Field".SetFilter("this")?

7. The+instruction+at+"0x1f90db9b"+referenced+memory+at+"0x7fa03794"+The+memory+cou

8. Record.open "", "URL=..."

9. """ IT MAY SAVE YOUR LIFE""""

10. "IIF" , "AND", and "aliases"

11. ""URGENT "" Inserting "For xml explicit" results to a Temp table


 
Powered by phpBB® Forum Software