Solved

ANSI join in Sybase?

Posted on 2000-03-22
5
2,581 Views
Last Modified: 2008-02-20
Hello,

I'm experimenting a bit, and want to use ansi joins and outerjoins
in Sybase.

Suppose I have 2 tables

material material int
              version  int
              client     int

client      client    int
              name   varchar(50)


I try the following:

select  m.material
,          c.name
from    material m join client c on m.client = c.client
where  m.version = 3
go

select  m.material
,          c.name
from    material m left join client c on m.client = c.client
where  m.version = 3
go

I get syntax errors. Also when I try 'inner join', 'left join', 'right join'.

Of course I know how to get what I want using transact sql
but I wonder if it is possible using ansi... that is both join and
outerjoin.

Anyone?

Arjan
0
Comment
Question by:ahoor
  • 2
  • 2
5 Comments
 
LVL 2

Expert Comment

by:jkotek
Comment Utility
What version of Sybase database you work with?

For Adaptive Server Enterprise - only version 12.0 supports 'left outer join' and 'right outer join' syntax. 'inner join' is not supported. Pre-12.0 versions (11.9.2...) support only T-SQL synax.

For Adaptive Server Anywhere - all 'joins' are supported.
0
 
LVL 3

Author Comment

by:ahoor
Comment Utility
I use version 11.0.3, but am (indeed) browsing
through some documentation of ASE12.0.

I thought ansi would be generic?
0
 
LVL 1

Expert Comment

by:bfrancis
Comment Utility
jktok is correct, here is info from the Sybase 12 manual:
Chapter 1 "New Features in Adaptive server 12"

ANSI Joins
Previous versions of Adaptive Server only provided syntax for a Transact-SQL join, which included the =* and *= symbols for specifying an right or a left join, respectively. Adaptive Server 12 includes the ANSI syntax for joining tables or views, which allows you to write either:

Inner joins, in which the joined table includes only the rows of the inner and outer tables that meet the conditions of the on clause. The result set of a query that includes an inner join does not include any null supplied rows for the rows of the outer table that do not meet the conditions of the on clause. The syntax for an ANSI inner join is:

select  select_list
from table1 inner join table2
on  join_condition

For example:

select au_id, titles.title_id,  title, price
from titleauthor inner join titles
on  titleauthor.title_id = titles.title_id
and  price > 15

Outer joins, in which the joined table includes all the rows from the outer table whether or not they meet the conditions of the on clause. If a row does not meet the conditions of the on clause, values from the inner table are stored in the joined table as null values. The where clause of an ANSI outer join restricts the rows that are included in the query result. ANSI syntax also allows you to write nested outer joins. The syntax for an ANSI outer join is:

select select_list
from table1 {left | right} [outer] join
        table2
on  predicate
 [join restriction]

For example:

select au_fname, au_lname,  pub_name
from authors left join publishers
on  authors.city = publishers.city

Sybase recommends that applications use ANSI outer joins because they unambiguously specify whether the on or where clause contains the predicate, which is ambiguous when you use Transact-SQL outer joins.

0
 
LVL 2

Accepted Solution

by:
jkotek earned 50 total points
Comment Utility
About so-called 'ANSI SQL' standard...

There are several (3?) ANSI SQL standards defined and each consists of several 'levels'. For example ASE 11.x is fully compliant with ANSI SQL 89 and with entry-level ANSI 92. The last version of ANSI SQL is from 1999 and is called 'SQL 3'.

As with every industry standard even ANSI SQL was in time of its creation obsolente - standard is something database vendors settle on.

Each db vendor wants to enhance functionality of its database beyond  agreed functionality. And that is the point where Transact SQL kicks in. T-SQL is Sybase enhancement to ANSI SQL 89, that provide a lot of things, among them the "outer join" functionality, which is not in ANSI 89 (using the =*).

However the Sybase was overruled when defining the ANI 92 and 'standard' is to use words "left outer join". So Sybase has to add this to their database servers. Same goes to trailing spaces in fixed-lenght strings (eg. Char(5)) - this has allready changed from 11.0.x to 11.5+, but can be forced back to non-ANSI behaviour (11.0.x) by a traceflag.

This dilema is even worse - take null in unique index - according to the ANSI there can be any number of null values in unique column ("null = null" results in null, which is NOT "true" value). In Sybase there can be only one null value in such column.
This is where Sybase copies behaviour of Oracle (db leader in ERP area) - just because all ERP applications (BAAN, PeopleSoft, SAP) expect database to behave this way.

So what is a 'standard' ;-)
0
 
LVL 3

Author Comment

by:ahoor
Comment Utility
Thanks, I guess this will be all response to my question... since the question was about Ansi more than about Subase12 I'm accepting this comment.

Too bad Sybase 12 will not be backwards compatible if one follows the advice, I discussed
it with my manager and we will not change the outerjoins in our procedures...

Again, thanks for the remarks.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

HOW TO: Connect to the VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere (HTML5 Web) Host Client 6.5, and perform a simple configuration task of adding a new VMFS 6 datastore.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now