View Definition with a Join

You can create a view that is derived from more than one base table.

Here is an example of a view derived from both the authors and the publishers tables. The view contains the names and cities of the authors that live in the same city as a publisher, along with each publisher’s name and city:

create view cities (authorname, acity, publishername, pcity) 
as select au_lname, authors.city, pub_name, publishers.city 
from authors, publishers 
where authors.city = publishers.city