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