Beginner Guide to Views in SQL: 🧵👇
1. Definition
- View contains a subset of records from a table based on given conditions
- Consider View as a Virtual table with restricted data, it is not a physical table
- View contains a subset of records from a table based on given conditions
- Consider View as a Virtual table with restricted data, it is not a physical table
2. Usecases
- Consider a situation where we don't want to give full access to the base table
- In such case, we can create a view which can have limited rows and columns from the base table
- Later, View can be used as a table in all DQL statements
- Consider a situation where we don't want to give full access to the base table
- In such case, we can create a view which can have limited rows and columns from the base table
- Later, View can be used as a table in all DQL statements
3. How to Create a View?
- Syntax:
CREATE VIEW <view-name> AS <query>;
- Using the above syntax, we can create views
- View always needs a select query in which we can mention the columns that need to be shown and the condition to filter the records
- Syntax:
CREATE VIEW <view-name> AS <query>;
- Using the above syntax, we can create views
- View always needs a select query in which we can mention the columns that need to be shown and the condition to filter the records
4. Example
- Let's create a View to see only employees living in Mexico and Hide the salary column in a view
- As shown in the above syntax, let's add a query with selected columns and filter conditions as shown below
Eg:
- Let's create a View to see only employees living in Mexico and Hide the salary column in a view
- As shown in the above syntax, let's add a query with selected columns and filter conditions as shown below
Eg:
- Here, EMPLOYEE_TABLE containing all records of employees including the salary of all employees and the city they live in, as shown below
- And, also see the records in View as a normal table via a Select query
Eg:
- And, also see the records in View as a normal table via a Select query
Eg:
5. Edit and Delete a View
Edit:
- CREATE OR REPLACE <view> from <query>
Delete:
- DROP VIEW <view-name>
Edit:
- CREATE OR REPLACE <view> from <query>
Delete:
- DROP VIEW <view-name>
6. Advantages
- Easily hide the complexity of the query and the user can directly interact with a view like a table
- Hide raw data and show aggregated data
- Apply row/column level security
- Easily hide the complexity of the query and the user can directly interact with a view like a table
- Hide raw data and show aggregated data
- Apply row/column level security
7. Disadvantages
- View cannot be created on temporary tables
- It cannot have parameters
- Cannot update records in view if the underlying query contains aggregate functions, Distinct, group by, union, etc
- Depending on the DB vendor it can have many more limitations
- View cannot be created on temporary tables
- It cannot have parameters
- Cannot update records in view if the underlying query contains aggregate functions, Distinct, group by, union, etc
- Depending on the DB vendor it can have many more limitations
That's a wrap!
If you find this helpful, then you'll also like reading my previous thread on SQL Indexing:
If you find this helpful, then you'll also like reading my previous thread on SQL Indexing:
Thank you for Reading!
I help people get started with Backend Development.
Follow me @vikasrajputin for more.
If you find this thread helpful then Like/Retweet the first tweet below:
I help people get started with Backend Development.
Follow me @vikasrajputin for more.
If you find this thread helpful then Like/Retweet the first tweet below:
جاري تحميل الاقتراحات...