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
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.
ON clause contains join conditions. The
WHERE clause can contains conditions that restrict which rows are returned.
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.
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).
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, the
RIVERS 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.