What Exactly are SQL Views?

You probably know about “ordinary views” already, but I’m sure you’ll find one or two things in this article that you haven’t thought about in this way yet…

What exactly are SQL views?

Views in SQL are a means of treating complex queries in the same way as “ordinary” tables. In fact, SQL is all about tables (which are bags of records), much like relational algebra is all about relations (which are sets of tuples).

There are different types of views:

“Ordinary” views

These are most commonly referred to as “views”. Most databases allow for declaring them using this syntax

CREATE VIEW my_view AS 
SELECT col1, col2
FROM my_table
WHERE ...

These stored views are then part of the catalog and can be referenced by name just like tables, which is great for re-use. And what’s even greater, you can grant a different set of privileges to views than to tables, which allows you to implement a complete security layer only using views (e.g. hiding some columns, or rows from certain users)

Some databases (including Oracle, PostgreSQL) even allow for updating them under certain circumstances – mostly when they’re unambiguous, 1-1 mappings of a single table that does not produce any calculations or denormalisations.

Materialized views

Just like the above “ordinary views”, materialized views can be used just like tables. In fact, they are tables as their data is materialized on disk, updated whenever their content is updated. These are useful for frequent, complex queries on rarely updated data.

Just add the MATERIALIZED keyword and you’re set:

CREATE MATERIALIZED VIEW my_view AS 
SELECT col1, col2
FROM my_table
WHERE ...

Among others, Oracle and PostgreSQL support materialized views. Other databases like SQL Server know of “indexed views”, which are a bit less powerful as you have to explicitly “materialise” your view data in indexes.

“Snapshot” views

These aren’t really views, they’re real tables. But in the context of this blog post, you could think of them as a permanently materialized “snapshot” view of your data. You can create such views using different syntaxes:

Most databases, e.g. Oracle

CREATE TABLE my_view AS
SELECT col1, col2
FROM my_table
WHERE ...

Some databases, e.g. SQL Server

SELECT col1, col2
INTO my_view
FROM my_table
WHERE ...

The nice thing about this approach is the fact that like materialized views, these “views” can be very useful for frequent querying – you have to pre-calculate the data only once. But once you’ve calculated that data, you generate a “snapshot” of it, and the data can continue to live independently from your view – just like a snapshot! (don’t forget to add relevant indexes, though)

Note that some databases including DB2 and Oracle support real SQL:2011 standard “snapshots”, such as flashback query in Oracle, or time travel queries in DB2. That’s a different story, though.

Parameterized views

Few people refer to these views as “views”, but if you think about it, that’s what they really are. Table-valued functions are stored procedures that return tables that can then again be used in SQL. For example (using PostgreSQL syntax):

CREATE FUNCTION my_view (arg1 INTEGER, arg2 INTEGER)
RETURNS TABLE (
    col1 INTEGER
    col2 INTEGER
)
AS $$
BEGIN
    RETURN QUERY
    SELECT col1, col2
    FROM my_table
    WHERE v1 = arg2 AND v2 = arg2;
END
$$ LANGUAGE plpgsql;

And then…

SELECT *
FROM my_view (42, 1337)
WHERE ...

That’s quite powerful, isn’t it? Among others, Firebird, HANA, HSQLDB, Oracle, PostgreSQL, SQL Server support table-valued functions.

Common Table Expressions

Like ordinary views, these views are named but they’re scoped only for a single statement – mostly a SELECT statement, although PostgreSQL or SQL Server also allow for common table expressions to be used with other DML statements. These “views” can be written as such:

WITH 
    my_view_a AS (
        SELECT ...
    ),
    my_view_b AS (
        SELECT ...
    )
-- To be consumed immediately by a statement
SELECT *
FROM my_view_a, my_view_b

While common table expressions are very useful for structuring code (they’re like “table variables”), they come with a price in Oracle or PostgreSQL, as the view is most often temporarily materialized, which prevents a lot of SQL transformations in the optimiser. On the flip side, common table expressions can be recursive / hierarchical, which is great for graph / tree traversal.

Derived tables

The most common type of views (although rarely called “views”) are derived tables, i.e. all nested select statements that are put in a FROM clause. E.g.:

SELECT *
FROM (
    SELECT ...
) my_view_a, (
    SELECT ...
) my_view_b

Unlike common table expressions, derived tables cannot be reused easily within a statement, but chances are high that they can be optimised into a different statement that has a higher performance.

Conclusion

SQL is all about tables and recomposition of tables in ad-hoc queries. The most important clause of any SQL statement is the FROM clause. It specifies the set of tuples that you want to recompose, filter, group, project in various ways. As we have seen above, you can feed any such table transformation easily into yet another transformation via one of the above ways to create views.

Curious about more? Read our popular article “10 Easy Steps to a Complete Understanding of SQL

jOOQ Newsletter: April 30, 2014

Subscribe to this newsletter here

Tweet of the Day

Our customers, users, and followers are sharing their love for jOOQ to the world. Here are:

Santiago M. Mola who appreciates jOOQ’s affinity to SQL features and its correctness

Vlad Mihalcea who’s reading the jOOQ docs to learn about SQL

Thanks for the shouts, guys! It looks our attempts to make our users competent and enthusiast SQL aficionados are effective. If you, our customers, are productive with Java and SQL, then we did our job right. Last but not least:

Tom Bujok who’s is now a happy SQL aficionado.

Want awesome free jOOQ stickers yourself? Contact us!.

Book Promotion – 10% off SQL Performance Explained

This book is a must-read for every SQL developer! Invest a very reasonable amount of money and around six hours of quality reading time, and you’ll stop wasting days and days of confused SQL tuning guesswork, because once you understand indexes, you are able to solve 90% of your performance issues.

Get your copy of SQL Performance Explained now! Use the limited-time “jOOQ” coupon to get a 10% partner discount:

http://sql-performance-explained.com

Community Zone – The jOOQ aficionados have been active!

In last week’s newsletter, we’ve praised Petri Kainulainen’s latest piece of work, his jOOQ / Spring / CRUD tutorial:

But that was not his last shout. He’s already promised another tutorial explaining how to do sorting and pagination with jOOQ:

Luckily for us Markus Winand – our SQL Performance Partner jumped in and reminded us of the difference between OFFSET pagination and KEYSET pagination. More details about these tools in his book – see above.

Michael Hughes from codinginthetrenches.com has also been discovering the benefits of using a SQL DSL, which he has explained in his article here. We always like it when jOOQ users realise the power of using jOOQ with stored procedures. You will be indefinitely more efficient compared to the standards JDBC or JPA.

Krisztian Horvath from SequenceIQ has published a post about using jOOQ with HBase – why not? If you’re restricting yourself to using plain SQL, you can use the jOOQ Open Source Edition with a lot of databases, although you’re going to be missing out on the coolest features and productivity boosters, of course.

Harmeet Singh appreciates the jOOQ code generator in his introductory blog post, an important point to mention. jOOQ’s code generator has helped our customers avoid so many errors already at compile-time, instead of waiting for them to appear in production, at runtime.

SQL Zone – When to use views

There are a couple of great reasons why you should occasionally (or systematically) use views instead of tables. These reasons include:

  • Views provide abstraction over tables. You can add/remove fields easily in a view without modifying your underlying schema.
  • Views can model complex joins easily.
  • Views can hide database-specific stuff from you, e.g. using Oracle’s SYS_CONTEXT for security checks.
  • Views can be useful for managing GRANTS, and thus hiding tables from you.
  • Views can help you with backwards compatibility when you change the underlying schema, but leave the (versioned) views in place.
  • Views can implement an additional security layer, e.g. by using Oracle’s WITH CHECK OPTION directly in the view.

See the relevant Stack Overflow question here.

SQL Zone – Calculating a running total

We’ve said it before. Use your database for your calculations, when this is reasonable. And by this, we don’t just mean simple COUNT(*) or SUM(AMOUNT) calls. We cannot stress the fact that you should be using window functions enough.

Imagine you want to do something fancy like calculating a balance value on every bank transaction based on the current balance and each transaction’s amount:

| ID   | VALUE_DATE | AMOUNT |  BALANCE |
|------|------------|--------|----------|
| 9997 | 2014-03-18 |  99.17 | 19985.81 |
| 9981 | 2014-03-16 |  71.44 | 19886.64 |
| 9979 | 2014-03-16 | -94.60 | 19815.20 |
| 9977 | 2014-03-16 |  -6.96 | 19909.80 |
| 9971 | 2014-03-15 | -65.95 | 19916.76 |

You can do this in SQL! Check out our recent blog post about how to calculate a SQL standard running total directly in SQL!

Upcoming Events

The next weeks are a bit quiet, but we’re going to be active again in May and June! Have you missed any of our previous jOOQ talks? Soon you’ll get another chance to hear us talk about jOOQ or SQL in general in any of these upcoming events:

Stay informed about 2014 events on www.jooq.org/news.