Views in sql

Views in sql

What are the opinions?

VIEWS are virtual tables that do not store any data of their own, but display data stored in other tables. In other words, VIEWS are nothing but SQL queries. A view can contain all or several rows of a table. A MySQL view can display data from one or more tables.

View syntax

Let's now look at the basic syntax used to create a view in MySQL.

CREATE VIEW `view_name` AS SELECT statement;


  • "CREATE VIEW` view_name`" tells the MySQL server to create a view object in the database with the name `view_name`
  • "AS SELECT statement" are SQL statements that are to be packaged into views. This can be a SELECT statement that can contain data from one or more tables.

Let's now create our first view using "myflixdb", we will create a simple view that limits the columns visible in the members table.

Let's assume that the authorization requirements indicate that the accounting department can only see the member number, name, and gender from the member table. To do this, you can create a VIEW −

CREATE VIEW `accounts_v_members` AS SELECT `membership_number`,`full_names`,`gender` FROM `members`;

Running the above script in MySQL Workbench for myflixdb and expanding the view node in the database explorer gives us the following results.

Note that the account_v_members object is now visible in the database view objects. Let's now execute a SELECT statement that selects all fields from the view.

SELECT * FROM `accounts_v_members`;

Running the above script in MySQL Workbench against myflixdb gives us the following results shown below.

membership_number full_names gender
one Janet Jones Female
2 Janet Smith Jones Female
3 Robert Phil Male
4 Gloria Williams Female
5 Leonard Hofstadter Male
6 Sheldon Cooper Male
7 Rajesh Koothrappali Male
eight Leslie Winkle Male
9 Howard Wolowitz Male

Only authorized columns for accounting were returned. Other details found in the members table have been hidden.

If we want to see the SQL statements that make up a particular view, we can use the script shown below to do so.

SHOW CREATE VIEW `accounts_v_members`;

Running the script above gives you the name of the view and the SQL SELECT statements used to create the view.

Connections and Views

Let's now look at a fairly complex example that includes multiple tables and uses joins.

We will package the generated JOIN that gets information from three (3) tables, namely members, movies, and movie rentals. Below is a script that helps us achieve this.

CREATE VIEW `general_v_movie_rentals` AS SELECT mb.`membership_number`,mb.`full_names`,mo.`title`,mr.`transaction_date`,mr.`return_date` FROM `movierentals` AS mr INNER JOIN `members` AS mb ON mr.`membership_number` = mb.`membership_number` INNER JOIN `movies` AS mo ON mr.`movie_id` = mo.`movie_id`;

Running the above scripts creates a view called general_v_movie_rentals in our myflixdb

Now let's select all the fields from the table named general_v_movie_rentals.

SELECT * FROM `general_v_movie_rentals`;

Running the above script in MySQL Workbench against myflixdb gives us the following results shown below.

membership_number full_names title transaction_date return_date
one Janet Jones Pirates of the Caribbean 4 20-06-2012 28-06-2012
one Janet Jones Forgetting Sarah Marshall 22-06-2012 25-06-2012
3 Robert Phil Forgetting Sarah Marshall 22-06-2012 25-06-2012
2 Janet Smith Jones Forgetting Sarah Marshall 21-06-2012 24-06-2012
3 Robert Phil X-Men 23-06-2012 28-06-2012

Note that we didn't have to write a complicated JOIN query to get the members, movies, and movie rental details. We just used the view in a regular SELECT statement like any other regular table. The view can be called from anywhere in the application system running on top of myflixdb.

Dropping Views

The DROP command can be used to remove a view from a database that is no longer required. The basic syntax for discarding a view is as follows.

DROP VIEW ` general_v_movie_rentals `;

Why use views?

You may want to use views primarily for the following 3 reasons

  • Ultimately, you will use your knowledge of SQL to create applications that will use the database for data requirements. We recommend that you use the original table structure in your VIEWS application instead of using the tables themselves. This ensures that when you refactor your database, your legacy code will see the original schema through the view without breaking the application.
  • VIEWS increase reusability. You won't need to create complex queries that include joins repeatedly. All the complexity is converted into a single query string using VIEWS. This shortened code will be easier to integrate into your application. This will eliminate the possibility of typos, and your code will become more readable.
  • VIEWS help in data protection. You can use views to show only authorized information to users and hide sensitive data such as credit card numbers.


  • Views are virtual tables; they do not contain the data that is returned. The data is stored in the tables referenced by the SELECT statement.
  • Views improve database security by showing only intended data to authorized users. They hide sensitive data.
  • Views make life easier because you don't have to write complicated queries over and over again.
  • You can use INSERT, UPDATE and DELETE for VIEW. These operations will modify the underlying VIEW tables. The only consideration is that the VIEW must contain all the NOT NULL columns of the tables it refers to. Ideally, you should not use VIEWS to update.