Sybase SQL Anywhere - KEY JOIN - Join Tables Related by Foreign Key

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;

Sybase SQL Anywhere (Sybase ASA) KEY JOIN clause:

Syntax	table1 KEY JOIN table2
Primary Key	Required
Foreign Key	Required
JOIN without ON	table1 JOIN table2 without ON clause is equal to KEY JOIN

Last Update: Sybase SQL Anywhere 12

Using KEY JOIN Sybase SQL Anywhere automatically generates ON condition based on primary and foreign key columns.

Primary key is required, otherwise you will not be able to define a foreign key in the child table:

   -- Define a parent table without specifying primary key
   CREATE TABLE states
   (
      abbr CHAR(2) NOT NULL, 
      name VARCHAR(90)
   );
 
   -- Define a child table
   CREATE TABLE cities
   (
      name VARCHAR(90),
      state CHAR(2) REFERENCES states (abbr)
   );
   -- Could not execute statement.
   -- Table 'states' has no primary key
   -- SQLCODE=-118, ODBC 3 State="42000"

Foreign key is also required, otherwise KEY JOIN fails:

   -- Define a parent table
   CREATE TABLE states
   (
      abbr CHAR(2) NOT NULL PRIMARY KEY, 
      name VARCHAR(90)
   );
 
   -- Define a child table without specifying foreign key
   CREATE TABLE cities
   (
      name VARCHAR(90),
      state CHAR(2)
   );
 
   -- Querying tables using KEY JOIN (cities table does not have foreign key) 
  SELECT c.name, s.name 
  FROM cities c KEY JOIN states s;
  -- Could not execute statement.
  -- There is no way to join 'c' to 's'
  -- SQLCODE=-146, ODBC 3 State="42000"

Let's insert sample data to the parent and child tables that have a foreign key:

   -- Insert a row to the parent table
   INSERT INTO states VALUES ('CA', 'California');
 
   -- Insert rows to the child table
   INSERT INTO cities VALUES ('Mountain View', 'CA');
   INSERT INTO cities VALUES ('Santa Clara', 'CA');

Now you can join the table using KEY JOIN without specifying primary and foreign key columns in ON clause:

   -- Querying tables using KEY JOIN 
  SELECT c.name AS city, s.name AS state
  FROM cities c KEY JOIN states s;

Query result:

Note that using JOIN without ON clause is equivalent to specifying KEY JOIN:

   -- Querying tables using JOIN without ON clause
  SELECT c.name AS city, s.name AS state
  FROM cities c JOIN states s;

The query returns the same result as KEY JOIN:

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. Each dimension table is joined to the fact table based on foreign key relationship.

For example, assume you have games table (fact table), contestants and stadiums tables (dimension tables):

   -- 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
   );

Let's insert sample data:

   -- 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');

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

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

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

The query returns:

KEY JOIN in other Databases:

Oracle:

KEY JOIN

Not supported

Specify ON clause explicitly. More...

• Reference and Articles

Sybase SQL Anywhere 12.0 Manual

• KEY JOIN