View in SQL

Views in SQL are kind of virtual tables. 
A view also has rows and columns as they are in a real table in the database. 

We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain condition.


Create View:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

UPDATING VIEWS

There are certain conditions needed to be satisfied to update a view. If any one of these conditions is not met, then we will not be allowed to update the view.

1. The SELECT statement which is used to create the view should not include GROUP BY clause or ORDER BY clause.
2. The SELECT statement should not have the DISTINCT keyword.
3. The View should have all NOT NULL values.
4. The view should not be created using nested queries or complex queries.
5. The view should be created from a single table. If the view is created using multiple tables then we will not be allowed to update the view.

Use of a View

Views are used for security purposes because they provide encapsulation of the name of the table. Data is in the virtual table, not stored permanently. Views display only selected data.

Getting Information about the Views

We can use the System Procedure Sp_Helptext to get the definition about the views. For example, we can use the sp_helptext command to get the information about the view Vw_EmployeeProj.

Hide   Copy Code

sp_helptext Vw_EmployeeProj

Renaming the View

We can use the sp_rename system procedure to rename a view. The syntax of the sp_rename command is given below:

Hide   Copy Code

SP_Rename 'Old Name', 'New name'

For example, if we want to rename our view View_Employeeinfo to Vw_EmployeeInfo, we can write the sp_rename command as follows:

Hide   Copy Code

sp_rename 'View_Employeeinfo', 'Vw_EmployeeInfo'

Dropping a View

We can use the Drop command to drop a view. For example, to drop the view Vw_EmployeeInfo, we can use the following statement:

Hide   Copy Code

Drop view Vw_EmployeeInfo

1. Views can hide complexity

If you have a query that requires joining several tables, or has complex logic or calculations, you can code all that logic into a view, then select from the view just like you would a table.

2. Views can be used as a security mechanism

A view can select certain columns and/or rows from a table, and permissions set on the view instead of the underlying tables. This allows surfacing only the data that a user needs to see.

3. Views can simplify supporting legacy code

If you need to refactor a table that would break a lot of code, you can replace the table with a view of the same name. The view provides the exact same schema as the original table, while the actual schema has changed. This keeps the legacy code that references the table from breaking, allowing you to change the legacy code at your leisure.


These are just some of the many examples of how views can be useful.




Difference between stored procedure and View

A Stored Procedure:

• accepts parameters
• can NOT be used as building block in a larger query
• can contain several statements, loops, IF ELSE, etc.
• can perform modifications to one or several tables
• can NOT be used as the target of an INSERT, UPDATE or DELETE statement.

A View:

• does NOT accept parameters
• can be used as building block in a larger query
• can contain only one single SELECT query
• can NOT perform modifications to any table

Difference between Join and Union

The join such as INNER JOIN or LEFT JOIN combines columns from two tables while the UNION combines rows from two queries.

In other words, join appends the result sets horizontally while union appends result set vertically.

The following picture illustrates the main difference between UNION and JOIN:

Conclusion:

Both being data combining operations are used in different situations. JOIN is used when we want to combine attributes of two relations having at least one attribute in common. UNION is used when we want to combine the tuples of the two relations that are present in the query.