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.