Queries and tables and views, oh my

1. Queries and tables and views, oh my

We've covered different ways to limit results. Now let's examine where your query finds its data.

2. Query

Queries reference data in a FROM clause. This clause references a table, but not all tables are actually tables. Queries can reference data from both base tables and temporary tables. Queries can also reference data from views. Views include standard views and materialized views.

3. Base table

Tables are organized storage that contain data. They have an extract, transform, load, or ETL, process that loads the data into the storage space. The data is loaded from a source system such as a human resources program, a client management system, a survey collection tool, et cetera.

4. Temporary table

A temporary table is a specific type of table. Temporary tables generally mirror base tables. They are organized storage and contain data. However, they are loaded from a query. They are created, loaded, and exist for the duration of the database session. Temporary tables ultimately come from data in existing base tables. You learned how to create a temporary table in a prior lesson, and the basic syntax is shown here.

5. Standard view

Views differ from tables because they are not data storage, but rather stored queries. Instead of containing data, a view contains a view definition which is directions to the data. When referenced in a query, the view runs the instructions to find and transform the data. The data ultimately comes from data in existing tables. Views are useful for a variety of reasons. A view can combine commonly joined tables, to simplify future queries and avoid redundant work. A view can create columns of common calculations such as summary statistics. Or a view can show partial information, such as employee names while hiding salary information.

6. Materialized view

A materialized view is a specific type of view. Materialized views are a cross between standard views and temporary tables. Like a view, it is a stored query. Unlike a view, it contains data. The data comes from a refresh process that runs the view definition at some defined interval. The refresh process is similar to how base tables are loaded with data using an ETL process. The view definition is materialized into data. Like temporary tables, materialized views ultimately use data from existing tables. Materialized views are useful for the same reasons as standard views. However, the data refresh process allows them to operate faster than standard views.

7. Summary of FROM clause references

A table is the base data storage. However, tables can be big and slow to query. Temporary tables can speed query performance when working with slow data. A database object may come from many sources or involve complicated logic to create the columns. Generally speaking, tables should not involve calculations or logic. In these instances, a view is more appropriate. For instance, a view can have a calculated column like a customer's first order date. If the logic creating a view is complex enough, queries referencing this view are slow. Scheduling data refreshes, thus materializing the view, fixes this slowness. Materialization retains the complexity of a view while capturing the speed of a table.

8. Information schema

Knowing if a query references a table or a view is not always clear. Luckily, databases like Postgres, SQL Server, and MySQL, have an information schema. This schema has metadata. One example is called tables. This query shows how to find the table type for the customer_table in the orders_schema. Tables shows if an object is a base table, temp table, or view.

9. Let's practice!

Now it's your turn to differentiate between views and tables.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.