Using Nested Joins

From InterBase

Go Up to Joining Tables (Embedded SQL Guide)


The SELECT statement FROM clause can be used to specify any combination of available tables or table references, parenthetical, nested joins whose results tables are created and then processed as if they were actual tables stored in the database. Table references are flexible and powerful, enabling the succinct creation of complex joins in a single location in a SELECT.

For example, the following statement contains a parenthetical outer join that creates a results table with the names of every city in the CITIES table even if the city is not associated with a country in the COUNTRIES table. The results table is then processed as the left table of an inner join that returns only those cities that have professional sports teams of any kind, the name of the team, and the sport the team plays.

DECLARE SPORTSCITIES CURSOR FOR
SELECT COU.COUNTRY, C.CITY, T.TEAM, T.SPORT
FROM (CITIES CIT LEFT JOIN COUNTRIES COU ON COU.COUNTRY =
CIT.COUNTRY) INNER JOIN TEAMS T ON T.CITY = C.CITY
ORDER BY COU.COUNTRY;

For more information about left joins, see Using Outer Joins.

Advance To: