Using Nested Joins
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.