SQL Views

The theory

SQL Views are virtual tables. They represent the result set of stored database queries, which users can in turn query just as they would query tables.

SQL Views vs the Views module

SQL Views 

SQL Views are the result of predefined, stored database queries. They are sometimes described as virtual tables because they can be addressed with SQL queries in the same way standard tables are queried. However, the data contained in SQL views is calculated on-the-fly when access to a view is requested.

Example: creating an SQL view

CREATE VIEW {view_name} AS SELECT something FROM {table_name};

Example: querying data from an SQL View

SELECT something FROM {view_name};

Reasons why someone would want to use SQL Views rather than querying the underlying tables directly include (but are not limited to):

• SQL Views can join several tables into a single, easy to use virtual table.
• SQL Views can be set up to only represent a subset of the data in the underlying tables. As such they can limit the degree of exposure of the underlying data to the outside world. 
• SQL Views can represent complex aggregated data (sums, averages, means, medians, ...) into easily accessible virtual tables, thereby hiding the complexity from the end users.

SQL Views are stored as individual objects in the database. Though you can use Drupal's database functions to select data from them, SQL Views are otherwise not known within Drupal unless you integrate them with the Views module or use something like the Data module to expose them to some extent.

Example: querying data from an SQL View with db_query():

$query = db_query('SELECT something FROM {view_name}');

Learn more about SQL Views: http://en.wikipedia.org/wiki/View_(SQL)
 

The Views module

The Views module can be described as an advanced web based SQL query builder with excellent abilities to fetch content from the database and represent it on the site in different formats (list display, table display, ...) as pages, blocks, rss feeds, .csv export files, and so on.

Just like SQL Views, Views built with the Views module are predefined SQL queries rather than actual sets of data. The result set of views (the actual data) is retrieved every time the View is executed. Depending on how your caching is set up, the data is retrieved in real-time from the database, or fetched from a Drupal cache.

Learn more about the Views module: https://drupal.org/project/views
 

Integrating SQL Views with the Views module

If you want to expose your SQL Views to the Views module, you can do so by implementing hook_views_data, the same way as you would expose a custom database table to the Views module.