Views in SQL

Views in SQL are the virtual tables. They have rows and columns like they are present in the normal database tables. But it fetches selective portion of the data from one or more tables. A view is a named query stored in the database catalog.

Creating View

To create a new view you use the CREATE VIEW statement. Syntax for creating view is

CREATE VIEW view_name
AS
SELECT column_list
FROM table_name [WHERE condition];

Above statement creates a view having name view_name. A view belongs to a database. By default, a new view is created in the default database. To create the view explicitly in a given database, use db_name.view_name syntax to qualify the view name with the database name.

Now, you can reference the view as a table in SQL statements. For example, to query data from the view_name view use the SELECT statement.

SELECT * FROM view_name;

MySQL executes the underlying query specified in the view’s definition when used with SELECT statement. A view can retrieves data from one or more tables. This picture illustrates a view based on columns of multiple tables:

CREATE VIEW MarksView
AS
SELECT table1_name.NAME, table1_name.ADDRESS, table2_name.MARKS
FROM table1_name, table2_name
WHERE table1_name.NAME = table2_name.NAME;

Example

Below example demonstrate how to creating view. A view cityView is created from employee info table. Later the created view is used with SELECT statement to retrieve record from the table.

# Create Table
CREATE TABLE employleinfo(id int, firstName varchar(50), lastName varchar(50), age int, city varchar(50));

# Insert record into the table
INSERT into employleinfo values(1,   "Rahul", "Bhagwat",20,"Noida");
INSERT into employleinfo values(2,   "Stephen",   "Fleming",   17,  "Delhi");
INSERT into employleinfo values(3,   "Sebastian", "Smith",     19,  "Patna");
INSERT into employleinfo values(4,   "Anjali",    "Sharma",    50,  "Mumbai");
INSERT into employleinfo values(5,   "Erica",     "Edwards",   40,  "Chennai");
INSERT into employleinfo values(6,   "Priya",     "Gowda",     50,  "Pune");
INSERT into employleinfo values(7,   "Shekar",    "Chandra",   43,  "Delhi");
INSERT into employleinfo values(8,   "Shekar",    "Sharma",    19,  "Patna");

# Create view
CREATE VIEW cityView 
AS
SELECT firstName, lastName, city
FROM employleinfo;

# Query from view
SELECT * FROM cityView;

# Output:
# Rahul|Bhagwat|Noida
# Stephen|Fleming|Delhi
# Sebastian|Smith|Patna
# Anjali|Sharma|Mumbai
# Erica|Edwards|Chennai
# Priya|Gowda|Pune
# Shekar|Chandra|Delhi
# Shekar|Sharma|Patna