Creating Equi-joins

Go Up to Using Inner Joins

An inner join that matches values in join columns is called an equi-join. Equi-joins are among the most common join operations. The ON clause in an equi-join always takes the form:

ON t1.column = t2.column

For example, the following join returns a list of cities around the world if the capital cities also appear in the CITIES table, and also returns the populations of those cities:

EXEC SQL
DECLARE CAPPOP CURSOR FOR
SELECT COU.NAME, COU.CAPITAL, CIT.POPULATION
FROM COUNTRIES COU JOIN CITIES CIT ON CIT.NAME = COU.CAPITAL
WHERE COU.CAPITAL NOT NULL
ORDER BY COU.NAME;

In this example, the ON clause specifies that the CITIES table must contain a city name that matches a capital name in the COUNTRIES table if a row is to be returned. Note that the WHERE clause restricts rows retrieved from the COUNTRIES table to those where the CAPITAL column contains a value.