MySQL CREATE VIEW Statement

MySQL is a popular open-source relational database management system (RDBMS) that is usually used for developing scalable and high-performance databases. MySQL was developed by MySQL AB (currently owned by Oracle Corporation) in 1995.

MySQL is known for its robust, easy, and reliable features with quick processing speeds. MySQL is generally used by dynamic web applications and is commonly used by languages such as PHP, Python, and other server-side programming languages.

In this article, you will learn about how to CREATE a VIEW in MySQL. You will learn how the CREATE VIEW Statement works along with some examples.

MySQL CREATE VIEW Statement

In relational database management systems (RDBMS) like MySQL, a view is a virtual table interactive with data generated from one or more underlying tables through either a defined query. Unlike a regular table, the view as a query doesn’t store the data itself. Instead, it creates a result set when someone queries it. The VIEWS are created using SELECT queries.

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, . . . . column_n

FROM table_name

WHERE condition1, condition2, . . . . , condition_n;

Explanation: In the above syntax you can see that we are creating a VIEW using the CREATE VIEW statement. The VIEW contains the selected columns from the table. WHERE condition can be specified or not it is not necessary.

Examples of MySQL CREATE VIEW Statement

Let’s take an example of the EMPLOYEE table having EMP_ID, NAME, AGE, and SALARY as columns and another table EMPLOYEE1 having EMP_ID, PHONE, and CITY as columns.

CREATE TABLE EMPLOYEE (
    EMP_ID INT PRIMARY KEY,
    NAME VARCHAR(50),
    AGE INT,
    SALARY INT
);


CREATE TABLE EMPLOYEEE1(
EMP_ID INT PRIMARY KEY,
PHONE INT,
CITY VARCHAR(20)
);

Insert the data on it:

INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, SALARY) VALUES
(1, 'Sahil', 21, 15000),
(2, 'Alen', 22, 13000),
(3, 'John', 22, 14000),
(4, 'Alex', 20, 13000),
(5, 'Mathew', 22, 14000),
(6, 'Sia', 21, 15000),
(7, 'David', 22, 16000),
(8, 'Tim', 21, 14000),
(9, 'Leo', 20, 15000),
(10, 'Tom', 21, 16000);


INSERT INTO EMPLOYEE1 (EMP_ID, PHONE, CITY) VALUES
(1, 6412356, 'Pune'),
(2, 6815633, 'Mumbai'),
(3, 6313656, 'Pune'),
(4, 6522356, 'Delhi'),
(5, 6789356, 'Mumbai'),
(6, 6863456, 'Chennai'),
(7, 6412152, 'Pune'),
(8, 6412956, 'Delhi'),
(9, 6412756, 'Pune'),
(10, 6412456, 'Chennai');

EMPLOYEE Table:

EMPLOYEE table

EMPLOYEE1 Table:

EMPLOYEE1 table

Examples of MySQL CREATE VIEW Statement

Example 1: CREATE VIEW without using the WHERE clause

Let’s CREATE a VIEW without using the WHERE clause

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, . . . . column_n

FROM table_name

WHERE condition1, condition2, . . . . , condition_n;

Let’s CREATE a VIEW from EMPLOYEE table where the view should have only EMP_ID and SALARY of Employees.

Query:

 CREATE VIEW view1 AS
  SELECT EMP_ID, SALARY
  FROM EMPLOYEE;

Output:

view1

Explanation: Here we are creating a VIEW named view1 from the EMPLOYEE table which will have EMP_ID and SALARY as columns. The VIEW will have all the rows present as we have not specified the WHERE condition. As there are 10 rows in the EMPLOYEE table the VIEW will also have 10 rows present in it.

Example 2: CREATE VIEW using WHERE Clause

Let’s CREATE a VIEW using WHERE clause

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, . . . . column_n

FROM table_name

WHERE condition1, condition2, . . . . , condition_n;

Let’s CREATE a VIEW from EMPLOYEE table where the VIEW should have only EMP_ID, AGE, and SALARY of Employees whose SALARY=14000

Query:

CREATE VIEW view2 AS
SELECT EMP_ID, AGE, SALARY
FROM EMPLOYEE
WHERE SALARY=14000;

Output:

view2

Explanation: Here we are creating a VIEW named view2 from the EMPLOYEE table which will have EMP_ID, AGE, and SALARY of Employees whose SALARY=14000. The VIEW will have only 3 rows present as there are only 3 Employees with SALARY=14000.

Example 3: CREATE VIEW without using WHERE clause having columns of 2 TABLES

Let’s CREATE a VIEW without using WHERE clause having columns of 2 TABLES

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, . . . . column_n

FROM table_name

WHERE condition1, condition2, . . . . , condition_n;

Let’s CREATE a VIEW that will have EMP_ID and NAME from EMPLOYEE table and PHONE and CITY from the EMPLOYEE1 table without using WHERE clause.

Query:

CREATE VIEW view3 AS
SELECT a.EMP_ID, a.NAME, b.PHONE, b.CITY
FROM EMPLOYEE a
JOIN EMPLOYEE1 b ON a.EMP_ID=b.EMP_ID;

Output:

view3

Explanation: Here we are creating a VIEW named view3 which is having EMP_ID and NAME from the EMPLOYEE table and PHONE and CITY from the EMPLOYEE1 table. Here it will have only those rows which match EMP_ID from EMPLOYEE table and EMP_ID from the EMPLOYEE1 table. As there are 10 rows from both the tables which have EMP_ID same Let’sthe view3 will have 10 rows present in it.

Example 4: CREATE VIEW using WHERE clause having columns of 2 TABLES

Let’s CREATE a VIEW using the SyntaxWHERE clause having columns of 2 TABLES

SyntaxLet’s:

CREATE VIEW view_name AS

SELECT column1, column2, . . . . column_n

FROM table_name

WHERE condition1, condition2, . . . . , condition_n;

Let’s CREATE a VIEW which will have EMP_ID and NAME from EMPLOYEE table and PHONE and CITY from EMPLOYEE1 table WHERE the CITY of the Employee is ‘Pune‘.

Query:

CREATE VIEW view4 AS
SELECT a.EMP_ID, a.NAME, b.PHONE, b.CITY
FROM EMPLOYEE a
JOIN EMPLOYEE1 b ON a.EMP_ID=b.EMP_ID
WHERE b.CITY='Pune';

Output:

view4

Explanation: Here we are creating a VIEW named view4 which has EMP_ID and NAME from the EMPLOYEE table and PHONE and CITY from the EMPLOYEE1 table WHERE the CITY of the Employee is ‘Pune‘. Here it will have only those rows which match EMP_ID from EMPLOYEE table and EMP_ID from EMPLOYEE1 table. Then once the EMP_ID is matched it will check for the Employees whose CITY = ‘Pune‘. As there are 10 rows from both the tables which have EMP_ID same but there are only 4 Employees whose CITY= ‘Pune‘. So the view will have 4 rows present in it.

Conclusion

MySQL CREATE VIEW Statement is used to CREATE a virtual table by using the SELECT queries. There are many advantages of VIEWS as it provides data abstraction, simplified querying, enhanced security, and performance optimization. They give users a comfortable space where they can involved in all the complex logic without getting in touch with the table. Hence, they serve as a middleman to manipulate the data in any intuitive way at their convenience. Investigating through views will help to maintain a high level of stability, reinforcing safety as well as keeping queries efficient when it comes to setting up a MySQL database.