Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Creating Views with Multiple Underlying Objects

Another issue in how views work is the underlying objects. As you've seen, views can be based on one or many underlying objects. The objects can be connected with joins and/or subqueries and can be tables and/or views.

Using Subqueries and Joins

Here is an example of a view definition that includes three joins and a subquery. It finds the author ID, title ID, publisher, and price of each book with a price that's higher than the average of all the books' prices. (Including the author ID means that you'll see more than one row for books with multiple authors.)

SQL

 create view highaverage as select authors.au_id, titles.title_id, pub_name, price from authors, titleauthors, titles, publishers where ...