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:
city | state |
Mountain View | California |
Santa Clara | California |
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:
city | state |
Mountain View | California |
Santa Clara | California |
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:
id | played | host | visitor | name | city | country |
1 | 2012-03-28 | AC Milan | FC Barcelona | Stadio Giuseppe Meazza | Milan | Italy |
2 | 2012-03-28 | Olympique de Marseille | FC Bayern Munich | Stade Velodrome | Marseille | France |