Creating SQL VIEWS: Explained Step-by-Step

We use SQL or Structured Query Language to communicate with a relational database and create SQL views.

Now, what are SQL views? They are virtual tables that combine and store data from actual database tables that you create. In turn, you get secure and simplified access to your dataset while hiding the underlying table structures and implementations.

So, how does this work? You already know that SQL queries are used for creating SQL views. When the SQL view is created, the underlying SQL query is stored. When you use an SQL query to look up data from multiple tables, the query is executed and helps fetch data from corresponding database tables. Similarly, when you query a created SQL view, the stored query is first fetched and then executed.

How Is Creating SQL Views Useful?

As SQL views are backed by stored SQL queries, they help in making your database secure. They also simplify complex queries and increase your query performance, among other benefits. Let’s see how creating SQL views can be beneficial to your database and your applications:

#1. Provides Database Security

SQL views act as virtual tables and hide the structures of the actual database tables. So, just by looking at a view, it’s not possible to know how many tables are there and what are the different columns in the table. Additionally, you can add access control restrictions to your database so that a user can only query the view and not the actual tables.

#2. Simplifies Complex Queries

Often times your SQL queries span across multiple tables with complex join conditions. If you’re using such complex queries frequently, you can leverage the power of creating SQL views. You can store your complex query in your SQL view. With this, you can simply query the view rather than having to run the entire complex query.

#3. Mitigates Against Schema Change

If your underlying table structures are changed, like the addition or removal of columns, then your views remain unaffected. If you’re relying on your SQL views for all your queries, you don’t have to worry about the schema change. Being virtual tables, your created SQL view will continue to work seamlessly.

#4. Improves Query Performance

When you’re creating SQL views, the database engine optimizes the underlying query before storing them. A stored query runs faster than running the same query directly. Thus, using SQL views to query your data gives you better performance and faster results.

How to Create SQL Views?

For creating SQL views, you can use the CREATE VIEW command. A view contains a SELECT statement. This gives the query that’ll be stored in the view. The syntax is as follows:

CREATE VIEW view_name AS 
SELECT 
  column_1, 
  column_2, 
  column_3...
FROM 
  table_name_1, table_name_2
WHERE 
  condition_clause

Let’s understand this using an example. Consider that you’ve created two tables department and employee. The department holds the name of the department and its ID. Accordingly, the employee table contains the name and ID of the employee, along with the ID of the department they belong to. You’ll use these two tables for creating your SQL view.

Create Your Database Tables

If you’re using MySQL, you can create this table using the following query:

CREATE TABLE `department` (
  `department_id` int,
  `department_name` varchar(255),
  PRIMARY KEY (`department_id`)
);

Insert data into the table you’ve created using the below SQL:

INSERT INTO department VALUES (1, 'Finance');
INSERT INTO department VALUES (2, 'Technology');
INSERT INTO department VALUES (3, 'Business');

With the department table ready and populated with data, create the employee table using the below MySQL query:

CREATE TABLE `employee` (
  `employee_id` INT, 
  `employee_name` VARCHAR(255), 
  `department_id` INT, 
  FOREIGN KEY (`department_id`) REFERENCES department(department_id)
);

Next, insert the employee records into your table. Do keep in mind that since the department_id column has a foreign key reference with the department table, you cannot insert a department_id that is not present in the other table.

INSERT INTO employee VALUES (100, 'John', 3);
INSERT INTO employee VALUES (101, 'Mary', 1);
INSERT INTO employee VALUES (102, 'Natalya', 3);
INSERT INTO employee VALUES (103, 'Bruce', 1);

Query Your Database Tables

Let’s use the tables in a database query. Consider the case where you have to query for the employee_id, employee_name, and department_name for all employees. In this case, you need to use both tables and join them using the common column, which is department_id. Your query thus becomes:

SELECT
  employee_id,
  employee_name,
  department_name
FROM 
  employee,
  department
WHERE 
  employee.department_id = department.department_id;

Create Your SQL View

Now, it may so happen that you’re frequently looking up or referencing this data. In addition to this, over time, with more records in your tables, your query time would start increasing. In such a scenario, you can create the SQL view corresponding to this query.

Use the following query to create a view named employee_info:

CREATE VIEW employee_info AS 
SELECT
  employee_id,
  employee_name, 
  department_name 
FROM 
  employee, 
  department 
WHERE 
  employee.department_id = department.department_id;

With this view in place, you can directly query the same. Your query is hence simplified to:

SELECT 
  * 
FROM 
  employee_info;

Querying the SQL view gives you the same output as running your original query. However, your query is now easy to maintain. The view hides the complexities of your query without any compromise on the result or performance.

How to Replace SQL Views?

If you have a view with a particular name and you want to modify or replace it, then use the CREATE OR REPLACE VIEW command. Using this, you can modify the SELECT statement that was used to create the view. Hence, your view output is replaced while keeping the view name intact. Additionally, you create a new SQL view if it does not exist.

You can replace SQL views using the following syntax:

CREATE OR REPLACE VIEW view_name AS 
SELECT 
  new_column_1, 
  new_column_2, 
  new_column_3 ...
FROM 
  new_table_name_1, 
  new_table_name_2 ...
WHERE 
  new_condition_clause

You can understand this better with an example. Consider the department and the employee tables. An employee_info SQL view is created from them, which contains the employee_id, employee_name, and department_name columns.

Considering security concerns, you might want to have the employee_id removed from this view. Additionally, you’d also want to change the column names to hide the actual database columns. You can make these changes to the existing view using the following SQL query:

CREATE OR REPLACE VIEW employee_info AS
SELECT
  employee_name as name,
  department_name as department
FROM
  employee,
  department
WHERE
  employee.department_id = department.department_id;

Once the view has been replaced, you’ll get different results when you use the same query you were using before. For example, in the query result, you’ll find that one of the columns has been dropped. In addition to that, the column names have been modified from employee_name and department_name to name and department, respectively.

Leverage the Power of Creating SQL Views

Creating SQL views will not only get you simplified queries and faster data lookup but also security and protection against schema changes. Creating SQL views is quite straightforward. You can take any existing query you have and turn that into an SQL view.

Use the step-by-step guide above to create SQL views and utilize their advantages. With SQL views, you can make your database applications simple and secure while increasing their performance.

If you want to manage your own SQL server, look at SQL Server Management Studio (SSMS).