KEY JOIN - Sybase SQL Anywhere to Oracle Migration

KEY JOIN clause allows you joining 2 or more tables based on their foreign key relationship, and does not require specifying columns and ON condition.

Quick Example:

   -- Define a parent table
   CREATE TABLE states
   (
      abbr CHAR(2) NOT NULL PRIMARY KEY, 
      name VARCHAR(90)
   );
 
   -- Define a child table
   CREATE TABLE cities
   (
      name VARCHAR(90),
      state CHAR(2) REFERENCES states
   );
 
  -- Querying tables using KEY JOIN 
  SELECT c.name, s.name 
  FROM cities c KEY JOIN states s;

For more information, see KEY JOIN in Sybase SQL Anywhere (Sybase ASA).

Conversion summary:

	Sybase SQL Anywhere	Oracle
KEY JOIN		Specify ON clause explicitly
Primary Key	Required	Not required
Foreign Key	Required	Not required

Last Update: Sybase SQL Anywhere 12.0 and Oracle 11g

Oracle does not support KEY JOIN, so you have to add the ON condition explicitly:

Oracle:

   -- Querying tables using KEY JOIN in Oracle
  SELECT c.name, s.name 
  FROM cities c KEY JOIN states s;
  -- ERROR at line 2:
  -- ORA-00933: SQL command not properly ended
 
  -- Specify ON clause
  SELECT c.name, s.name 
  FROM cities c JOIN states s ON c.state = s.abbr;
  -- Executed successfully

Note that JOIN without ON clause and KEY JOIN are equivalent in Sybase SQL Anywhere:

Sybase SQL Anywhere:

  SELECT c.name, s.name 
  FROM cities c KEY JOIN states s;
 
   -- is equivalent to JOIN without ON condition
  SELECT c.name, s.name 
  FROM cities c JOIN states s;

And they both have to be converted to Oracle as:

Oracle:

  -- Add ON clause
  SELECT c.name, s.name 
  FROM cities c JOIN states s ON c.state = s.abbr;

In Sybase SQL Anywhere, you can use KEY JOIN to join more than 2 tables using a table expression list (a comma-separated list of tables).

It is similar to star join, a join between a fact table and a number of dimension tables.

For example, assume there are games table (fact table), contestants and stadium tables (dimension tables) defined as follows:

Sybase SQL Anywhere:

   -- Dimension table
   CREATE TABLE contestants
   (
      id INTEGER PRIMARY KEY,      
      host VARCHAR(90),
      visitor VARCHAR(90)
   );
 
   -- Dimension table
   CREATE TABLE stadiums
   (
      id INTEGER PRIMARY KEY,      
      name VARCHAR(90),
      city VARCHAR(90),
      country VARCHAR(90)
   );
 
   -- Fact table
   CREATE TABLE games
   (
      id INTEGER,      
      contestants INTEGER REFERENCES contestants,
      stadium INTEGER REFERENCES stadiums, 
      played DATE
   );
 
   -- Contestants
   INSERT INTO contestants VALUES (1, 'AC Milan', 'FC Barcelona');
   INSERT INTO contestants VALUES (2, 'Olympique de Marseille', 'FC Bayern Munich');
 
   -- Stadiums
   INSERT INTO stadiums VALUES (1, 'Stadio Giuseppe Meazza', 'Milan', 'Italy');
   INSERT INTO stadiums VALUES (2, 'Stade Velodrome', 'Marseille', 'France');
 
   -- Games
   INSERT INTO games VALUES (1, 1, 1, '2012-03-28');
   INSERT INTO games VALUES (2, 2, 2, '2012-03-28');

Then using KEY JOIN between games table (fact table), contestants and stadium tables (dimension tables) you can perform a star join:

Sybase SQL Anywhere:

  SELECT games.id, played, host, visitor, stadiums.name, city, country
  FROM games KEY JOIN (contestants, stadiums);

Note that the query does not contain ON clause and joined columns, Sybase defines them automatically.

In Oracle, you have to full specify join conditions using ON clause as follows:

Oracle:

  SELECT games.id, played, host, visitor, stadiums.name, city, country
  FROM games JOIN contestants ON contestants = contestants.id JOIN stadiums ON stadium = stadiums.id;

Both Sybase and Oracle queries return:

Converting KEY JOIN from Sybase SQL Anywhere to Oracle.

Sybase SQL Anywhere:

   -- Define a parent table
   CREATE TABLE states
   (
      abbr CHAR(2) NOT NULL PRIMARY KEY, 
      name VARCHAR(90)
   );
 
   -- Define a child table
   CREATE TABLE cities
   (
      name VARCHAR(90),
      state CHAR(2) REFERENCES states
   );
 
  -- Querying tables using KEY JOIN 
  SELECT c.name, s.name 
  FROM cities c KEY JOIN states s;

Oracle:

   -- Define a parent table
   CREATE TABLE states
   (
      abbr CHAR(2) NOT NULL PRIMARY KEY, 
      name VARCHAR2(90)
   );
 
   -- Define a child table
   CREATE TABLE cities
   (
      name VARCHAR2(90),
      state CHAR(2) REFERENCES states
   );
 
  -- Querying tables using JOIN and ON clause
  SELECT c.name, s.name 
  FROM cities c JOIN states s ON c.state = s.abbr;

Sybase SQL Anywhere:

   -- Dimension table
   CREATE TABLE contestants
   (
      id INTEGER PRIMARY KEY,      
      host VARCHAR(90),
      visitor VARCHAR(90)
   );
 
   -- Dimension table
   CREATE TABLE stadiums
   (
      id INTEGER PRIMARY KEY,      
      name VARCHAR(90),
      city VARCHAR(90),
      country VARCHAR(90)
   );
 
   -- Fact table
   CREATE TABLE games
   (
      id INTEGER,      
      contestants INTEGER REFERENCES contestants,
      stadium INTEGER REFERENCES stadiums, 
      played DATE
   );
 
  SELECT games.id, played, host, visitor, stadiums.name, city, country
  FROM games KEY JOIN (contestants, stadiums);

Oracle:

   -- Dimension table
   CREATE TABLE contestants
   (
      id NUMBER(10,0) PRIMARY KEY,      
      host VARCHAR2(90),
      visitor VARCHAR2(90)
   );
 
   -- Dimension table
   CREATE TABLE stadiums
   (
      id NUMBER(10,0) PRIMARY KEY,      
      name VARCHAR2(90),
      city VARCHAR2(90),
      country VARCHAR2(90)
   );
 
   -- Fact table
   CREATE TABLE games
   (
      id NUMBER(10,0),      
      contestants NUMBER(10,0) REFERENCES contestants,
      stadium NUMBER(10,0) REFERENCES stadiums, 
      played DATE
   );
 
  SELECT games.id, played, host, visitor, stadiums.name, city, country
  FROM games JOIN contestants ON contestants = contestants.id JOIN stadiums ON stadium = stadiums.id;

Sybase SQL Anywhere

• Reference and Articles
• KEY JOIN

Oracle

• Reference and Articles