SQL Programming language and SQL Database engines provide some useful mechanisms in order to make management of the data easier.
SQL View is one of them which can be used to create a table from a SQL result set.
What Is SQL View?
SQL View is a virtual table which does not exist in a database physically. SQL View table is created from an SQL statement which is generally a select result set. View have rows, columns just like a regular table. The fields are generally related with one or more fields from real tables in the database.
The syntax of the SQL View is simple and like a
SELECT statement. We will use
Create View with view name and
As statement like below. The
Select statement will bind related rows, columns and fields to the
SQL View table. We can also use conditional statements like
Create View VIEWNAME AS Select column1, column2, … From TABLENAME Where CONDITION;
- `VIEWNAME` is the name of the view we will create
- `column1`, `column2` etc are columns we will use in view from `TABLENAME`
- `TABLENAME` is the table name we want to feed our view. Provided columns will be selected from this table.
- `CONDITION` is optional with `Where` statement.
Create SQL View
We will start with a simple SQL View creation. We will create a view named
TurkishCustomers from the table named
Customers and use
Contact columns from this
Create View TurkishCustomers AS Select Name, Contact From Customers Where Country="Turkey";
Using SQL View
We have created the SQL View successfully. We can use it as a regular table. In this example, we will use
Select statement by providing the SQL View name as a table name which is
TurkishCustomers in this example.
Select * From TurkishCustomers;
Update SQL View
In some cases, the SQL View may have some changes. But as we use the View in a lot of SQL queries we just need to update the SQL View content like fields or conditions. We can use
Create Or Replace View statement. This statement will update the SQL View if it exists, if not the SQL View will be created from scratch.
In this example, we will update SQL View named
Create Or Replace View TurkishCustomers AS Select Name, Contact From AllCustomers Where Country="Turkey";
Drop or Remove SQL View
If we do not need the SQL View nay we can delete it with
Drop View statement. This can be useful if we will use the view name as a regular table. In this example we will delete or drop the SQL View
Drop View TurkishCustomers;