6.3. Types of VIEWs

The type of SELECT statement and its purpose can classify VIEWs. The strong advantage of a VIEW is that it will produce the correct results when it is invoked, based on the current data. Trying to do the same sort of things with temporary tables or computed columns within a table can be subject to errors and slower to read from disk.

6.3.1. Single-Table Projection and Restriction

In practice, many VIEWs are projections or restrictions on a single base table. This is a common method for security control by removing rows or columns that a particular group of users is not allowed to see. These VIEWs are usually implemented by in-line macro expansion, since the optimizer can easily fold their code into the final query plan.

Get Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.