Using Inner Joins

Go Up to Joining Tables (Embedded SQL Guide)

InterBase supports two methods for creating inner joins. For portability and compatibility with existing SQL applications, InterBase continues to support the old SQL method for specifying joins. In older versions of SQL, there is no explicit join language. An inner join is specified by listing tables to join in the FROM clause of a SELECT, and the columns to compare in the WHERE clause.

For example, the following join returns the department name, manager number, and salary for any manager whose salary accounts for one third or more of the total salaries of employees in that department.

EXEC SQL
DECLARE BIG_SAL CURSOR FOR
SELECT D.DEPARTMENT, D.MNGR_NO, E.SALARY
FROM DEPARTMENT D, EMPLOYEE E
WHERE D.MNGR_NO = E.EMP_NO
AND E.SALARY*2 >= (SELECT SUM(S.SALARY) FROM EMPLOYEE S
WHERE D.DEPT_NO = S.DEPT_NO)
ORDER BY D.DEPARTMENT;

InterBase also implements new, explicit join syntax based on SQL-92:

SELECT col [, col ...] | *
FROM <tablerefleft> [INNER] JOIN <tablerefright>
[ON <searchcondition>]
[WHERE <searchcondition>];

The join is explicitly declared in the FROM clause using the JOIN keyword. The table reference appearing to the left of the JOIN keyword is called the left table, while the table to the right of the JOIN is called the right table. The conditions of the join – the columns from each table – are stated in the ON clause. The WHERE clause contains search conditions that limit the number of rows returned. For example, using the new join syntax, the previously described query can be rewritten as:

EXEC SQL
DECLARE BIG_SAL CURSOR FOR
SELECT D.DEPARTMENT, D.MNGR_NO, E.SALARY
FROM DEPARTMENT D INNER JOIN EMPLOYEE E
ON D.MNGR_NO = E.EMP_NO
WHERE E.SALARY*2 > (SELECT SUM(S.SALARY) FROM EMPLOYEE S
WHERE D.DEPT_NO = S.DEPT_NO)
ORDER BY D.DEPARTMENT;

The new join syntax offers several advantages. An explicit join declaration makes the intention of the program clear when reading its source code.

The ON clause contains join conditions. The WHERE clause can contains conditions that restrict which rows are returned.

The FROM clause also permits the use of table references, which can be used to construct joins between three or more tables. For more information about nested joins, see Using Nested Joins.

Creating Equi-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.

Joins Based on Comparison Operators

Inner joins can compare values in join columns using other comparison operators besides the equality operator. For example, a join might be based on a column in one table having a value less than the value in a column in another table. The ON clause in a comparison join always takes the form:

ON t1.column <operator> t2.column

where <operator> is a valid comparison operator. For a list of valid comparison operators, see Using Comparison Operators in Expressions.

For example, the following join returns information about provinces in Canada that are larger than the state of Alaska in the United States:

EXEC SQL
DECLARE BIGPROVINCE CURSOR FOR
SELECT S.STATE_NAME, S.AREA, P.PROVINCE_NAME, P.AREA
FROM STATES S JOIN PROVINCE P ON P.AREA > S.AREA AND
P.COUNTRY = 'Canada'
WHERE S.STATE_NAME = 'Alaska';

In this example, the first comparison operator in the ON clause tests to see if the area of a province is greater than the area of any state (the WHERE clause restricts final output to display only information for provinces that are larger in area than the state of Alaska).

Creating Self-joins

A self-join is an inner join where a table is joined to itself to correlate columns of data. For example, the RIVERS table lists rivers by name, and, for each river, lists the river into which it flows. Not all rivers, of course, flow into other rivers. To discover which rivers flow into other rivers, and what their names are, theRIVERS table must be joined to itself:

EXEC SQL
DECLARE RIVERSTORIVERS CURSOR FOR
SELECT R1.RIVER, R2.RIVER
FROM RIVERS R1 JOIN RIVERS R2 ON R2.OUTFLOW = R1.RIVER
ORDER BY R1.RIVER, R2.SOURCE;

As this example illustrates, when a table is joined to itself, each invocation of the table must be assigned a unique correlation name (R1 and R2 are correlation names in the example). For more information about assigning and using correlation names, see Declaring and Using Correlation Names.

Advance To:

• Using Outer Joins