CODEWITHSUNDEEP
sqlt-sqljoinsybase
Adam Tegen
Adam Tegen

Reputation: 25897

*= in Sybase SQL

I'm maintaining some code that uses a *= operator in a query to a Sybase database and I can't find documentation on it. Does anyone know what *= does? I assume that it is some sort of a join.

select * from a, b where a.id *= b.id

I can't figure out how this is different from:

select * from a, b where a.id = b.id

Upvotes: 11

Views: 23094

Answers (5)

jason saldo
jason saldo

Reputation: 9950

ANSI-82 syntax 

select 
    * 
from 
    a
  , b 

where 
     a.id *= b.id

ANSI-92

select 
    * 
from 
   a
  left outer join b 
      on a.id = b.id

Upvotes: 5

Adam Tegen
Adam Tegen

Reputation: 25897

select * from a, b where a.id = b.id

Requires that a row exist in where b.id = a.id in order to return an answer

select * from a, b where a.id *= b.id

Will fill the columns from b with nulls when there wasn't a row in b where b.id = a.id.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415810

Of course, you should write it this way:

SELECT *
FROM a
LEFT JOIN b ON b.id=a.id

The a,b syntax is evil.

Upvotes: 6

Adam Tegen
Adam Tegen

Reputation: 25897

From http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc34982_1500/html/mig_gde/mig_gde160.htm:

Inner and outer tables

The terms outer table and inner table describe the placement of the tables in an outer join:

  • In a left join, the outer table and inner table are the left and right tables respectively. The outer table and inner table are also referred to as the row-preserving and null-supplying tables, respectively.

  • In a right join, the outer table and inner table are the right and left tables respectively.

For example, in the queries below, T1 is the outer table and T2 is the inner table:

  • T1 left join T2
  • T2 right join T1

Or, using Transact-SQL syntax:

  • T1 *= T2
  • T2 =* T1

Upvotes: 16

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391376

It means outer join, a simple = means inner join.

*= is LEFT JOIN and =* is RIGHT JOIN.

(or vice versa, I keep forgetting since I'm not using it any more, and Google isn't helpful when searching for *=)

Upvotes: 9

Related Questions