Joins with SQL-Derived Tables

You can use the where clause to join a SQL-derived table and an existing table.

In this example, the two tables joined are dt_c, which is a SQL-derived table, and publishers, which is 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

You can also use outer joins with SQL-derived tables. SAP 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