Views in SQL

In this medium post, we will learn about views. This is a useful feature you can keep in your arsenal of tools.

Learning Objectives:

• Discuss how and when to use views with queries.
• Explain how to use the AS function with views.
• Explain the benefits and limitations of views.

Overview of Views.

• It is a stored query.
• Can add or remove columns without changing schema.
• Use it to encapsulate queries.
• The view is removed after the database connection has ended.

CREATE [TEMP] VIEW [IF NOT EXISTS]
view_name(column-name-list)
AS
select-statements;

Creating a View.

CREATE VIEW my_view
AS
SELECT
g1.pokemon_name,
g2.pokemon_name,
g2.pokemon_type,
g2.pokemon_id,
g2.pokemon_hp
FROM generation1 g1
LEFT JOIN generation2 g2
  ON g1.pokemon_name = g2.pokemon_name

You can now access this query using ‘my_view’. Which makes writing queries and understanding the SQL simpler.

We can now access this query using the following:

SELECT *
FROM my_view

Or we can DROP the view like so:

DROP VIEW my_view

Views allow you to write complex queries easily like so:

SELECT count(pokemon_id),
pokemon_name
FROM my_view
GROUP BY pokemon_name;

Consider using views next time you want to simplify multilevel queries so that your SQL code becomes easy to understand and easy to build on.

Do let me know what you think of this post. I am still a learner myself and I would love to hear your thoughts. You are more than welcome to message me on LinkedIn or Twitter.