Chapter 9: SQL Derived Tables

Joins with SQL derived tables

Example 1

The following example illustrates a join between a SQL derived table and an existing table. The join is specified by the where clause. The two tables joined are dt_c, a SQL derived table, and publishers, an existing table in the pubs2 database.

select dt_c.title_id, dt_c.pub_id
   from (select title_id, pub_id from titles) as dt_c,
        publishers
   where dt_c.pub_id = publishers.pub_id

The following example illustrates a join between two SQL derived tables. The two tables joined are dt_c and dt_d.

select dt_c.title_id, dt_c.pub_id
   from (select title_id, pub_id from titles) 
         as dt_c,
        (select pub_id from publishers) 
         as dt_d
   where dt_c.pub_id = dt_d.pub_id

Outer joins involving SQL derived tables are also possible. Sybase supports both left and right outer joins. The following example illustrates a left outer join between two SQL derived tables.

select dt_c.title_id, dt_c.pub_id
   from (select title_id, pub_id from titles) 
         as dt_c,
        (select title_id, pub_id from publishers) 
         as dt_d
   where dt_c.title_id *= dt_d.title_id

The following example illustrates a left outer join within a derived table expression.

select dt_titles.title_id 
   from (select * from titles, titleauthor
         where titles.title_id *= titleauthor.title_id)
dt_titles

Creating a table from a SQL derived table

View this book as PDF