SQL Views Explained: Deep Dive into Use Cases, Benefits & Best Practices

What are SQL Views?

A view is a virtual table based on the result of a SQL query. Unlike physical tables, views do not store data themselves. Instead, they store a query that retrieves data from underlying tables when executed. For a deeper exploration of SQL fundamentals and advanced techniques, consider reviewing the Comprehensive SQL Course: From Basics to Advanced Database Design.

Key Difference: Tables vs Views

| Feature | Table | View | |---------|-------|------| | Data Storage | Stores data physically | No physical data storage | | Performance | Faster (direct access) | Slower (executes underlying query each time) | | Maintenance | Hard to change | Easy to modify (change only the query) | | Data Modification | Read & write | Read-only |

Database Architecture: The Three Levels of Abstraction

Physical Level: Lowest level, managed by DBAs. Handles data files, partitions, logs, caches.
Logical Level: Where developers work. Defines tables, relationships, views, indexes, stored procedures.
View Level: Highest level, for end-users and applications. Exposes only relevant, customized information.

Six Critical Use Cases for SQL Views

1. Central Business Logic (Improve Reusability)

Instead of writing the same complex query (e.g., joining tables and aggregating) in every analysis, create one view that persists this logic.

Example: Create a monthly summary view so analysts can write simple queries like:

SELECT order_month, 
       SUM(total_sales) OVER (ORDER BY order_month) AS running_total
FROM v_monthly_summary;

2. Hide Complexity (Provide Abstraction)

When you have a complex database with cryptic table names and many relationships, create user-friendly views that combine relevant data.

Example: Create an order_details view that joins orders, products, customers, and employees into one easy-to-query object.

3. Implement Security (Column & Row Level)

Column-level security: Exclude sensitive columns (e.g., salary, personal data) from certain views.
Row-level security: Filter rows based on user roles (e.g., EU sales team can only see EU data).

Example:

CREATE VIEW sales.v_order_details_eu AS
SELECT ...
FROM ...
WHERE c.country != 'USA';

4. Dynamic Flexibility (Protect Users from Changes)

Provide stable views to users. When you change underlying tables (split, rename, etc.), update the view query so users' queries remain unaffected.

5. Multi-Language Support

Create translated versions of your data model for international teams by renaming views and columns.

6. Virtual Data Marts (Data Warehousing)

In data warehouse architectures, use views as data marts instead of physical tables. Benefits include:

No ETL between layers
Single source of truth
Easy to maintain and update
Quick to modify business logic

Views vs CTE: When to Use Which

| CTE | View | |-----|------| | Temporary (exists only during query execution) | Persisted in the database | | Reduces redundancy within one query | Reduces redundancy across multiple queries | | No maintenance needed | Requires creation and potential updates | | Better for one-off, query-specific logic | Better for reusable, important business logic |

How to Manage Views

Creating a View

CREATE VIEW sales.v_monthly_summary AS
(
    SELECT 
        DATE_TRUNC('month', order_date) AS order_month,
        SUM(sales) AS total_sales,
        COUNT(order_id) AS total_orders,
        SUM(quantity) AS total_quantity
    FROM sales.orders
    GROUP BY DATE_TRUNC('month', order_date)
);

Dropping a View

DROP VIEW v_monthly_summary;

Updating a View (SQL Server Example)

IF OBJECT_ID('sales.v_monthly_summary') IS NOT NULL
    DROP VIEW sales.v_monthly_summary;
GO

CREATE VIEW sales.v_monthly_summary AS
(
    -- new logic here
);

Note: In PostgreSQL, you can use CREATE OR REPLACE VIEW for easier updates. For more on PostgreSQL-specific features, see A Comprehensive Guide to PostgreSQL: Basics, Features, and Advanced Concepts.

Key Takeaways

Views persist logic, not data
They improve reusability, security, and abstraction in your projects
Use views when logic is important and reusable across multiple queries
Use CTEs when logic is temporary and important only for one query

For a more advanced discussion on SQL optimization and analytics, refer to Master SQL: Comprehensive Guide to Advanced Data Analytics and Optimization.

hey friends so today we're going to talk about a very important technique in SQL that you will be using in your SQL

projects we have the views now of course I can show you in two minutes how to create a view in SQL it's really

straightforward but in this video we're going to do beyond that because I really want you to understand why views are

very powerful in SQL and very effective for your SQL projects so we going to understand why views are such a big deal

so we're going to do a deep dive into the concept and usage in order to understand how to use views in real

world scenarios so we're going to kick off with the first topic where we're going to have a quick recap about the

database structure and the ddl language so let's go now if you is not like a query that

we can use in SQL it is an object that we can find in the database so before we jump immediately to the view I would

like to give you the big picture the whole structure of the database so let's go we have like hierarchy structure and

and the highest level of this hierarchy is the SQL Server the SQL Server manages multiple databases it's like the control

center that keep everything running and accessible now inside the SQL Server we have multiple databases so a database is

collection of formations that are stored in structure way it's where all your data is kept and organized in different

tables and objects and each database is separated from others and it has its own data now inside each database we can

find multiple schemas a schema is like a logical way on how you group up related objects like tables and Views together

within a database like for example if you have a database called sales we can group up different tables about the

orders underneath the schema orders and maybe we have like multiple views and tables about the customers where we can

put it in the schema customers so if you find like multiple tables and Views that are describing the same object the same

topic we put them all together underneath one schema so again a database could be like the sales

database and the HR database they are completely different types of data and underneath the sales we can have like

different sections we have the sections about the orders and sections about the customers and now moving on what we can

find inside the schema we can find tables a table is where actually your data is stored it contains multiple

columns and rows so it is where the data physically lives and now inside the schemas we have another type of object

we call it View and of course in this section we are focusing in the views so a view is like a virtual table that has

a structure and everything but inside it we don't have any data so the view does not store any data and in order to see

the data we have to execute the query behind the view and only after that we going to see some data but it is not

like the tables it doesn't store the data permanently now inside the tables we can Define multiple stuff like

columns and as well keys and the same same thing for the views inside the views we can Define multiple columns and

one last level for each column we have like a name and a data type so as you can see the databases are really

organized and we have like hierarchy where the top node is the SQL server and the lowest node is the columns and rows

so this is what we call the database structure now in order for you to build and manage this structure we have set of

commands we call it ddl the shortcut of data definition language so the detail is a set of commands that allow us to

Define and manage the structure of the database so we have commands like create where it help us to create databases

schemas tables views another command called alter of course after you create something you would like maybe later to

do changes and updates and of course we have the drop in order to remove any database object like dropping a schema

dropping a database tables views so as you can see the DL commands can help us to manage the database structure so from

this picture we have understood that we can create views inside schemas in the database so now if you check the client

at the object Explorer you can find the exact hierarchy so it start with the SQL Server this is our local server that run

at our machine and then we can find inside it multiple databases and one of them is our sales DB that you have

installed together with other database like the adventure works so now if you go to the sales DB over here you can go

and drill to the the next level and now we can find here a lot of objects and one of them that we know we have tables

and Views and now you might say okay but between the database and tables we have schemas so where are the schemas well

actually if you go inside the tables you can find our tables customers employees and so on but before it we have a name

called sales. customers and you can find it everywhere sales. customers sales to employees and so on the sales is the

schema that bring all those tables together and underne one logical schema so we have a database called sales DB we

have schema called sales and we have a table called customers and now if you would like to see all the schemas inside

this database what you can do you can go to the Securities over here and then here we have like a folder called

schemas if you go over there you will find the list of all schemas that we have in this database you might say but

we didn't create all those stuff we have only the sales that we know well as you create a database in SQL Server you will

get a lot of other system default schemas that the server going to create one of them is the

information schema where it holds a lot of views about the catalog and the metadata where you can find the list of

columns tables views and so on so here we have only one schema that we have created for the user it is the sales so

let's go back now if you go inside one of those tables you will find here multiple stuff like we have columns Keys

constraints and so on and if you go to the columns you will end up at the lowest level of the the hierarchy and

here we have the columns like the customer ID and we have some extra informations like the data type length

and so on so this is the structure and the hierarchy of databases now I would like you to

understand a fundamental concepts on the database in order to understand the views the three level architecture of

the database this architecture can describe the different levels of data abstractions in a database so let's see

what this means so the architecture is divided into three levels the first level is the physical level then we have

the logical level and the third one is the view level now let's understand each level what it means so now the physical

level it is the lowest level of the database where the actual data is stored in a physical storage and usually who

has access to this layer are the database administrators because they are the experts and they have to manage the

access and the security of this layer because they are the expert does have to manage a lot of stuff like optimizing

the performance making sure that everything is secure and managing the backup and recovery and to do all the

configurations and many other tasks so at the physical layer we have to deal with a lot of stuff like the data files

partitions logs cataloges blocks and caches and many other stuff that each database needs in order to store your

data so as you can see this layer is very complicated and you need to be really an expert of databases in order

to be able to manage all those stuff so we call this layer a physical layer or sometimes we call it an internal layer

so now let's move to the next level we have the logical level so The Logical layer it is less complicated than the

physical layer here at this level you have to deal on how to organize your data and normally we have here like an

application developer or we have like data Engineers that access The Logical level in order to define the structure

of your data so those developers going to focus on how to structure your data rather than how the data is exactly

storing the data physically at the storage so they don't have to deal with all those details they leave it for the

database administrator and they can focus only on how to structure the data that's why we need for this kind of role

an abstraction level for them which is The Logical level so now what actually the developers are doing at this level

well they are like creating tables and defining the relationships between those tables or they can go and Define views

they can create indexes on the tables in order to optimize the performance of the tables or maybe they are creating stored

procedures and functions and some other codes in order to manage those tables so as you can see they are building the

data model they are structuring your data but they don't care at all where are those data stored physically in the

database so as you can see here things are less complicated than the physical layer and it is perfect abstraction for

developers to build projects so we call this The Logical layer or sometimes we call it the conceptual layer okay so now

moving on to another level of abstraction we have the view level so the view level is the highest level of

abstraction in the database and it is what the end users and applications can access and can see so for example you

could have like one view for business analysts so you prepare and customize a views that are suitable only for the

business analyst and you might say you know what let's prepare another set of views that are suitable for data

visualizations and Reporting like you can go and connect for example a powerbi in order to create dashboards so they

are fully customized and prepared views in order to be connected with the RBI reports and you can keep doing that by

creating multiple set of views that are suitable for specific purpose and use case so as you can see at this level we

are exposing our data for multiple users and multiple applications so now the question is what do we have to deal at

the view level well you have there only views that holds only the relevant informations for the use case or users

so the users at this level have only views they don't have to deal with the tables indexes s procedures any files

logs partitions or anything this is the highest level of abstraction because the focus of this layer is is to make it

friendly for the end users and easy to consume so we call this layer The View layer or sometimes we call it an

external layer so this is the three level architecture of the databases or we call it the three abstraction levels

of the database so the physical layer has the highest complexity the lowest abstraction and the view layer has the

highest abstraction so this is one more reason why the views are very important Concept in SQL databases

okay so with that we have enough fundamentals in order to start talking about the views so the question is what

are views a view is a virtual table in SQL that is based on the result of a query without actually storing the data

in the database so in short this means views are stored or persisted SQL query in the database so let's understand what

this exactly means now so far what you have learned we have like database table all what you have done we create a

select query in order to retrieve the data from this table so once we execute our query we will get the result back

now if we are talking about views they have as well like the structure of the table but without any data inside it and

for each view there is like a query attached to it so there is no data but we have like a query in order to get

data we call the normal table as a physical table and the view we call it a virtual table so now how exactly we

going to get the data so now if you go and write query by selecting data from The View not from the table from The

View what can happen isql going to go and Trigger the queue that is attached to the view and this query is

responsible to query the physical table and then the result going to fill the structure of the view and we will get

back of course the results so we are directly querying a view but actually we are indirectly querying a physical table

so the view it's like between us and the data so that means my real data is stored inside the database tables and

the views are like an abstraction layer between me and my real data and of course the data will not be stored

inside the view each time I'm querying the view what's going to happen the SQL query behind the view going to be

executed again so it going to go and retrieve the data and get it back to the view and then I will see it in the

output so this is what we mean with SQL View so now let's have a quick comparison

between tables and Views tables stores the actual data physically at a database so the tables where the data is

persisted with in the other hand the views they are virtual tables and they do not store any data inside the

database but they present the data from the underlying tables so that means views don't persist any data physically

now the tables are hard to maintain and as well hard to change so it needs a lot of efforts in order to do any change

like adding columns and moving columns always requires a lot of efforts for the migration especially if you have large

tables but in the other hand the views are way easier to maintain and very flexible to change all what you have to

do is only to change the query of the view so that means you can very quickly change stuff in the views compared to

the tables but if you are talking about performance tables are faster than views for example if you go and do a simple

select on the table you will get the data back as soon as the database fites the data but if you are selecting

something from the view it is actually two queries the query that comes from the user and as well the second query is

the view query and the query of the view could be very complicated in order to extract the data from the underlying

table so selecting something from the view is always slower than selecting something from a table now if you have a

table you can read from a table and as well you can write to a table but the views are read only as the name says it

is only a view you cannot go and write something to the database using the view okay so those are the big differences

between views and tables all right so with that we have a clear understanding what are views but

now you might ask me why do we need views that's why now what we're going to do we're going to Deep dive into

multiple scenarios and use cases that you might encounter in your SQL projects so let's start with the first use case

the first use case and the core reason why we use views in our data projects is to store Central logic from a complex

query in the database so that everyone can access it and with that we improve reusability between multiple queries and

we reduce as well the complexity of the overall projects so let's understand what this means so now in our project we

have like two tables in the database orders and customers and we have learned previously that if we have like a

complex query we can go and use the CTE so for example in our CTE we are joining tables and doing some aggregations using

the sum and the city going to store the data in an intermediate result and then we have the main query for example we

are doing the step two where we are ranking the data so the whole thing is in one query and let's say that a

financial analyst was doing this type of analysis now what could happen if that you might have another user for example

a budget analyst where he is doing exactly the same First Step so he has as well a city query where first the data

are joined and then aggregated using the sum but the last step in the main query he's not doing ranking he's just doing

like Max and Min and not only that we have a third user the risk analyst were as well doing the same initial step

using the CTE joining the tables and doing the summarization but here the risk analyst in this scenario is just

comparing the data at the last step in the main query so now if you sit back and look at this you can see allthough

three data workers all of them are doing the same First Step so all of them are doing the same CTE they are joining the

data and then doing summarization and of course this is a complete waste of time that each one of them has to create

first to the CTE from the scratch in order to do some analyzis so it is complete redundancy and makes no sense

so this is exactly the disadvantage of only using CTE is in the projects now what we can do instead of that those

three data workers going to decide to say you know what let's put the first step as a view in the database so

instead of using CTE each time we're going to take this script and put it in the database so we have now a central

logic that is stored in the database where everyone can use it so we have this query this logic only once and

everyone can benefit from it so now the financial analyst instead of going directly to the physical tables they can

go to the view so that means she needs only to write one script the rank scripts same thing goes for the budget

analyst he has only to write the query for the Max and Min and as well for the risk analyst you just need to compare

the data so as you can see all those queries are reduced and they can only focus on the analyzes so this is exactly

the magic of views in data analytics this logic this knowledge can be centralized in the database and this is

way faster and better than having this logic written each time someone want to do any analyses so this is why we need

views in data projects so now if you compare views with CTE the CTE are used in order to

reduce their redundancy within one single query so it improves the reusability within one query where in

the other hand in the views we are reducing the redundancies from multiple queries so we are reducing the

complexity of the whole project so the views are improving the reusability in multiple queries now think about it like

this we use views in order to persist a logic in the database so the logic is so important that we want to persist it in

the database it's like in the tables we persist data but with the views we are persisting logic but in the other hand

in the CTE the logic is not persisted it is temporary and going to be calculated only on the Fly within the scope of one

query so this logic is important only in this scenario and it is not important for any other queries that's why it

makes no sense to persist it using the views so you have to decide is this logic is very important then take it

away from the CTE and put it in the view but if you think you know what this logic is not really important and only

important in this one query then stay with the CTE because creating views always needs some extra steps in order

to maintain the view you have to create the view you have to drop the view if you don't need it but the CTE There is

almost no maintenance for it the database going to do automatically the clean up once the query is done so there

is no extra activity to drop a CTE or something that's why CTE is easier to use than views so those are the big

difference between the views and CTE okay so now let's check quickly the syntax of a view so now we have a query

like select from where so this is a query a symol select statements but now in order to create a view an object in

database we have to go and use a ddl command create so we're going to say create view cuz we want to create a view

then the name of the view and then it's like the CTE we say as and then double parenthesis so as you can see is very

simple and we call this a ddl command where we are telling the database go and create a view and the logic of the view

comes from this query so it's very simple this is how you can create views in database okay so now let's have the

following task and it says find the running total of sales for each month I'm going to start this task by solving

it using the CTE so first I'm going to go and do few aggregations on the top of the month so let's go and select so now

what do we need we need the order dates but we need it as a month I'm going to go and use the date trunet like this and

say okay I would like to have the date as the grity of month so let's go and call it order month and now after that

we're going to do a few aggregations like for example let's go and get the sum of sales and we're going to call it

total sales and that's it for the start so now let's go and call it from the table sales orders and group by and we

are grouping a by by the month so something like this let's go and execute it and now for this we get for each

month the total sales and now the next step that we have to go and calculate the running total for the sales this is

of course not the running total so that means either we can go and use subqueries so this means this is our

first step and we need a second step so either use queries or CTE I will go with the CTE over here so I'm going to say

with CTE and monthly summary and we're going to Define it

like this and now what we're going to do we're going to go and Define the main query so the main query going to be

simple so select and let's go and get the order month and now we have to build the running total so we're going to go

and use the window function so sum total sales and then we're going to say over we don't have to partition the data we

will just sort it by the order month and we can leave it ascending so

this is the running total and we have to go and select of course our CTE from here so let's go and

execute it and with that we are getting running total of course we can go and add the total sales in the output in

order to understand the results so here in the output we are just building a cumulative sales so for this scope

everything is fine we are using the CTE but now imagine that this logic is important for multiple queries so it's

really nice to have such a report where we are aggregating the data at the level of the month and this could be used from

different users and different queries so now we say how about to put this logic in one view so that everyone can access

it and we don't have to repeat the same aggregations over and over and now before we put it in view someone comes

and say how about to add one more aggregation so that not only the total sales we can ow so now before we put it

as view maybe some other user says you know what we would like to have one more aggregation not only the total sales

let's make the scope little bit bigger so that everyone can benefit so for example we can go over here and say you

know what let's go and add the total number of orders so we can go over here and say count and let's get the order ID

and say this is the total orders or maybe some other says let's get the quantities as well so we can go and

summarize the quantity like this and we call it total quantities so with that we are like doing a lot of

aggregations on the month level let's go and execute only the CTE so now we have really nice report that is based on the

months and can be used from many different queries so now what we're going to do we're going to take this and

put it in a view let's go and select only this logic and create a new query and now what we're going to do we're

going to put our query here and we have to create now the ddl in order to create a view so it's going to be like this

create view let's give it the name maybe starts with the V underscore and this can be the monthly summary so this is

the name of the view and us then we put everything in parenthesis it's like you are building a CTE so we have here our

logic and here is our ddl query in order to create the view so now let's go and execute it now as you can see in the

output it says only that the command is completed because this is not a select query this is a ddl command so the SQL

get tell you okay either I created it successfully or not so now the question is where do I find now my view well if

you go to the object Explorer you can see over here underneath our database sales DB we have here something called

tables where we are used to query those tables but beneath it we have as well our views so if you check the views and

expand it now we are not seeing a view because we just created The View here so go over here and refresh and once you do

that you will see the newly created view so this is the one that we just created so now what we can do we can go and

create a new query and let's go and just query The View so select star from so V monthly summary let's go and excuse it

and now as you can see we are getting now the result of the view and I'm accessing now this logic from completely

external query so now I can think about the view as any other table that we have in the database and again the big

differences between the views and the tables the tables has data has actual data and everything there is persisted

but the view is just an abstraction for me and behind it there is like a query that goes to the table and query the

tables in order to present the results but for me I don't care about all those details I can go immediately to the

query over here and start querying so now in order to create the total running sales I don't have to create the CTE and

subqueries I just go and get for example our main query let's go back over here so now

instead of using the CTE I can go directly and access the view so as you can see now my query is very simple I'm

doing immediately the step two without having to prepare the data first so if I go and execute it I will get exact

results and now if you compare the query on top of the view like this with the CTE query you can see that the CTE has

more steps and it is like little bit more complicated ated than the query on top of the view and this is exactly the

benefit of the view we reduce the complexity and it is very easy to consume from the point of view of users

so this is how you can put your logic in central place using views and with that we have learned how we create a view now

one more thing about the schemas if you check our tables over here they have all one schema so we have sales. customers

sales. employees orders and so on our new view has the scheme of DB o if you create any object whether it's table or

View and you don't specify a schema in a default schema called dbo and now let's go back to our ddl scripts so as you can

see over here we didn't specify any schema we just said okay this is the view name and now in order to put our

view in the correct schema we don't want it to be in the default you have to go and specify the schema name in the ddl

and now in order to do that we go to the name of the view and we write the schema name and then separated with is a DOT so

the first one is the schema name and the second one is the view name so now let's go and execute it now if you check over

here you don't see anything new but if you're refresh you will find another view in the correct schema so we have

sales. V monly summary and this is exactly what we want so this is how you can assign a view or even a table to the

correct schema if you don't want to use the default one theb all right so now the next of that you say you know what I

would like to clean up I don't need those two views in my database so how to delete a view we can go and use the

command drop it is very simple if you go and create a new query and you say drop and then you say what you want to drop

you want to drop a view and then you have to specify the name and schema of the view but now since it is the default

schema DBU I don't have to write it down so we can start immediately with the view name so V

monthly summary so that's it it's very simple so now we go and execute it it says it's completed but as you can see

nothing is changed we go and refresh and now we can see that the database did remove the view with the schema DBU so

it's very simple this is how you can drop a view in SQL okay so now to the next step let's go back to our ddl of

creating the view sales monthly summary and now you say you know what I would like to change the logic inside the view

so how we can update this content how I can update my query if you say let's go and for example delete this column I

need only three columns so and you go execute it the database say I cannot do it for you because we have already such

a view so SQL will not go and replace stuff isq going to say no we have the same name and I cannot do anything for

it so how we can update the view well in other databases like postris for example it's very simple you can go over here

and say create or replace view so it's like you are telling the database create this view or if it already exists then

replace place it and you will not get error in the boss Crest but in the SQL Server it is little bit more complicated

we don't have these commands so here you have two ways either you go over here and say you know what let's first drop

the view so you go with the same name over here and then what you're going to do

you're going to go and mark the drop view so if you execute it like this the view going to be dropped and then we

recreate the view like this so what we have done we destroy the view and then we recreate it using the new logic or

you say you know what I would like to have everything in one goal like I don't want to do it in two steps I would like

to have everything in one command and for that you have to use in SQL Server the tsql the transact SQL it is like an

extension for SQL only in SQL Server well it's like programming where you can go and add variables or you can all go

and add checks we will not do a deep dive in this language but I would like to show you how to do it for the views

so just follow me with that I'm going to go and replace the whole thing and then we can say if and now we are checking

the system catalog if the object ID and now we go and specify the view name so let's go and copy the whole thing with

the schema as well and then we're going to say for SQL this is a view so if this object exist so we are saying is not

null so that means it exist in the catalog then what SQL should do should drop this view so we're going to say

drop View and it's like we have D first and then semicolon and then we say for SQL go and with that we are saying for

SQL that tsql is done so the logic is done and after that we have the ddl for our view so again what we are doing we

are checking before creating the view whether the view exist if it exist then we are telling SQL go and drop it and if

it doesn't exist that means we haven't created this view yet it is completely brand new view then this step going to

be skipped so there is nothing to drop so now if you go and execute the whole thing it will work and of course if you

go and refresh over here you still see the view so SQL did destroy the table first and then recreate it so if you

execute it again so this is how you replace your logic inter view in SQL server and with that we have learned all

possible scenarios how to create a view how to drop a view and how to update the logic of a

view now back to our data base architecture and let's understand how the database executes views so now let's

say that the data engineer is creating view called top end so the query can to be sent to the database engine and once

the database engine understand this is a view this is not a table so now the database engine going to go to the disk

storage and to the catalog and it will stores not only the metadata about the view also the SQL that is responsible

for the view so it's going to take the SQL statements that you have to find in the create View and place it as well in

the catalog so if you compare to the tables we have in the tables only metadata but in the views we have both

the metadata and as well the query of the view and as well you can see that the database engine will not create a

table in the user's data so there is nowhere data stored inside the dis or the cach so the actual data the physical

data will not be stored anywhere we are storing only metadata and the query inside the system catalog so now we tell

our data analyst okay we have a new view and the data analyst can go and write a query in order to retrieve the data from

The View so he going to say and say select from The View and execute it the database engine going to take it and

understand okay now we are talking about view so the database first has to retrieve not the data can retrieve the

query from the catalog in order to understand what do we have now to execute then the database going to

execute the query of the view first and the data for this query comes from a physical table called orders so now the

database engine is querying the orders to retrieve the data so that's we have a data for the end user and then it's

going to be executed and the result going to be sent back to the data analyst so as you can see there is like

two queries the SQL engine first has to execute the query from The View and only after that the database engine can

execute the query that comes from the user so actually the data comes always from a physical table but we are not

providing the data analyst an access to the table we are just providing an access to the view so this can happen

each time an end user selecting data from The View always the database engine going to grab the query from the catalog

executed first in order to get the data and then execute what the end user wants and now if the data engineer says no

let's go and drop the view so she writes a query in order to drop the view and the database engine going to go to the

system catalog and delete both the metadata and the query so as as you can see if you are dropping a view you are

not losing the actual data so there will be no user data lost at all so don't worry about it what you are losing is

only the query and the metadata about your view it's only if you drop a physical table like the orders you will

lose your data so droing views is not that bad like dropping a database table so this is how the database works with

the views behind the scenes now moving on to this second scenario to the next use case of using

views in projects is that we use views in order to hide complexity and to improve abstraction in many scenarios we

work with a very large and complex databases and we can use views in order to reduce the complexity and make things

easier for the users so let's understand what this means now I'm going to explain for you a scenario that happens almost

in each project like if you get an access to a database where you want to do analyzes you will be in scenario and

this can happen a lot where you're going to find a large database where the tables are very complex to understand

they have a lot of columns they have like Technical and cryptical names and how tables are connected to each others

and relationship between them it's almost impossible to understand then you have to be deeply involved with the data

models with documentations and with experts until you understand how to query this database so if you are not

the developer and From enduser perspective it's going to be nightmare where you are trying trying to do

multiple joints in order to make simple analyzis and of course from the database perspective this data model is good

enough for one application but if you are opening your database for multiple data analysis projects this can be a

nightmare because you have to go and explain for each user how to create the data so what we usually do instead of

giving a direct access to such Technical and hard to understand data model we go as developers creating multiple views

since we are the expert of the data model and these new views going to be an abstraction of the complexity that I

have in my database and we have to make sure that those views are providing objects that are friendly so they have

like a full English name that Mak sense and as well the columns are friendly and we try to not offer a lot of views so

the user don't have to do all the joins so we provide like few views that are friendly and has a lot of informations

that the users needs for the analyzers so with that the users can to have an access to something more friendly and

easy to consume and then they can write simple queries in order to do analyzis on top of these friendly views and this

is what we can give a name like we are providing a data product from my complex physical database so here again how

important are the views to provide an abstraction and easy to consume objects for the users and with that I can hide

all my complexity and the script of the view going to be developed from the experts and only once so that the users

don't have to understand or to write these complex SQL joins and with that you can make your data projects way

easier than before so this is another important use case for the views where we can use it in order to provide

abstraction and as well easy and friendly objects for the end users okay so now let's have the following task and

it says provide view that combines details from orders products customers and employees so now instead of having

all those tables from our database we have to provide one combined view that has everything well almost everything so

now let's see how we can create such a view so let's start first by the table orders I'm going to go and

select first star from sales orders and let's go and executed this is the central table that connects everything

you can see here we have the order ID product ID sales customers and so on so it is a great start point so now we're

going to go and be picky about the columns I would not show all the columns but I would say let's go and show for

example the order ID this is essential it's nice to have a unique identifier now the product ID I will not show it

but I will just list it over here the same for the customer ID salesperson ID those stuff I would like to replace

later so I will just make it as comment so I don't forget about it because it makes no sense to show the product ID

and customer IDs and so on we would like to show the details about each object because instead of having the product ID

I would like to show for example the product name itself and some other informations from the table products and

with that we are reducing the complexity so now what else we can get from the table orders we can go and get the order

date I will put it here and maybe we can go and get stuff like sales and quantity so like this of course we can

go and put all the columns but for now I will go with those informations now it's important since we're going to have a

lot of tables let's go and make sure we are using alas so I'm going to have the O for each of those columns all right

fine so now we have four details from the table orders now what is next we have the product ID so let's go and get

the informations from the products what we're going to do we're going to use a lift join just to make sure to not miss

any order if you go with the inner join you might Miss some orders so I will not do that so let's join it with the

products like this and so now we have to go and join the tables so we going to use the keys

product ID equal the order product ID all right so now the question is which informations we want to show for the

users let's go to the table orders so we have the product and category and the price I would say let's go and get the

product and category that's enough so now instead of the ID I'm going to have it like this so it's going to be the

product and the category now let's go and test it I'm going to execute it now as you can see

we don't have a product ID we have the product name which is more friendly so we have now those two columns from the

orders and those two from the products and the last two as well from the orders so it looks really nice and friendly and

with that the user don't need extra table called products we have everything in one now let's go and do the same for

the customers so let's go and do the same thing so let join sales customers see and as well join them

using the key customer ID equal to the customer ID now we have to go and grab a few columns from the customers let's go

and check so we have the first name last name and Country and score I would say I would go with the names and the

countries but instead of having first name and last name I'm going to put everything in one so we have to go and

con cut the informations so we're going to get the first

name then plus then empty between the first name and the last name and then the last name like

this now we will not call it a name we're going to go and call it the customer name because later we're going

to have as well an employee name all right so next we want to get the country and we have to say this is the country

from the customers so we're going to call it customer country and that's it let's go and execute it now we can see

we have again our orders products and now we have the informations from the customer but here you have we is show

that we have some nulls and that's because there is no last name so what we're going to do we're going to go and

handle the nulls for the last name and as well the first name so we're going to use the

Calis if the last name is null then make an empty string and the same thing for the first name so first name all right

so now let's go and execute it so with that we are getting as well the first name if the last name is missing or if

the first name is missing we can get the last name so looks good so it looks good with that we have the customers details

the last thing we have to go and get the employees so the employee here is called salesperson ID which we can connect it

directly to the table employees so if you go to the employees over here which columns do we need we have the first

name last name department and so on I would say let's go get the names and the departments so first let's go and join

it so lift join sales employees and we're going to join it using the employee ID and we're going to

join it with the sales person ID that comes from the order table so now instead of the person ID we're going to

have as well the same thing so I will just go and copy paste this so instead of the LC we're going to have e and as

well e over here and we're going to call it sales name and as well what we going to have

we're going to have the department so department and that's it let's go and excuse it so now we have a lot of

informations in our view so we have the first columns from the orders then from the products and here we have from

customers and those two from the employees and the last two again from the orders so with that we have combined

now all the relevant informations from multiple tables in our database in only one view this reason result is relative

big but still we have all the informations in one and it is more friendly for the users in order to

consume our data instead of going and joining like all those four tables together so now the next step we're

going to put the result of this query in a view in our database so that our end users can start consuming it so how

we're going to do it this is our combined query and now we're going to write the ddl for it so create View and

now we're going to give it the name order the details and then as and we're going to put the whole thing in two

parentheses so at the start and at the end and of course don't forget the schema so our schema is sales sales dots

then we have the view name just in order to have it in the correct schema and not in dbo so everything is ready let's go

and excuse it so now let's go and check our databas if you go and refresh you will find our second view order details

so now let's go and distance we're going to say select star from sales we order details let's go and execute it and with

that we are getting now a combined view that are showing all important informations from the database so this

is what the users can see and with that the users don't care about how many tables do we have in the tables and how

to join all those tables we have only one View and we can start working on it this is a very common use case for the

views okay and moving on to the next scenario to the next use case we use SQL views in order to implement security and

to protect our data in the database in many scenarios we have sensitive informations in our data and we cannot

go and share it with everyone so one of the best practices is to create views in order to protect your data before

sharing it with the users so let's understand what this means so now let's understand first the scenario without

views only tables so now let's say that you have the table orders four columns and three rows and then you have like

for example a manager that has an access directly to the database and start writing some queries in order to

retrieve data but in your project you have multiple people that has an access to your datab base like for example a

data analyst and as well she is writing a script in order to retrieve data from the orders and as well you have maybe a

students that has an access to your database and quering the data like any other rule like a manager and data

analyst so as you can see you have now different rules in your project and all of them having the same rights by

accessing directly your table so a manager or data analyst or student they are seeing the whole table all rows and

all columns and of course in the real projects this is a big problem sometimes the data are sensitive and you cannot

give an access for everyone and of course if you're are using only tables this going to be a nightmare because you

can go and create multiple tables but it's going to be really hard to make all those tables in but instead of that we

have views so what you can do you can go and remove all accesses to the physical table but instead you can go and create

multiple views for each role for example you can go and create a view called orders managers and maybe you can give

all the data and all the columns because the managers are allowed to see let's say sensitive data but still it's nice

to create a view maybe you change your mind later and you go and remove something now let's say that for the

data analyst you want to offer all the data but there is only one column that is very sensitive so what you can do you

can go and create another view called orders analyst so in the view only three columns are available a b c and then you

give access to all data analyst and with that you have protected this sensitive information so we call this column level

security and now we come to our poor students and here we create another view where we are not only protecting the

column D but also we are protecting few rows like for example the row number three because we want to offer only few

informations to the students so we are protecting the columns and as well the rows and for that we can create another

dedicated view called for example order students and we can offer it to the students and with that we are doing

column level security and as well roow level security so we are offering multiple views very easily without

having to worry how to load the data from one table to another so creating those views are really easy and provide

us a perfect tool in order order to manage the security of our data so this is one very common use case of using

views in data projects all right so now let's have the following task and it says provide a view for EU sales team

that combines details from all tables and excludes data related to the USA so the first part of the task is similar to

what we have already done but we cannot offer all data for the user so this time we are providing a view that is

specifically created for a team the sales team so the first part we have already done it where we are combining

all details in one view but the problem with the view that we have created that it shows all data but now the

requirement change we cannot show all data we have to go and exclude the USA Data from our details so let's see how

we can do that it's very simple we're going to go and grab the same query we will not repeat that so we have as well

here joining tables and prepare everything but instead of showing all data what we're going to do we're going

to go and filter the data based on the custom customer country so it's very simple at the end we will have a we

Clause where the C country is not equal to USA so we have now a filter let's go and

execute it and with that as you can see in the output we are getting the orders that are not from USA and with that we

are protecting the data of the USA and the EU sales teams can access only their data so it looks nice and protected and

with that we are doing now Ro level security that means we are hiding now all the orders all the rowes that are

not allowed to be seen and consumed from this group of users so now what is the next step it is very simple we're going

to go and put everything in one view so with that we have the query ready and we can go and create the new view so we're

going to call it create view then we need a schema and the name going to be almost the same so order details but EU

and then we have to have us and parenthesis like this so everything is ready let's go and executed and now we

can go and refresh in order to see our new view if you still don't see it you can go to the Views over here and

refresh as well to the folder so with that I can see we have our new view now of course the next step we go and test

it so let's create a new query select star from sales and V order details EU so

that's the tested and with that as you can see we are getting the combined view only for the data that is relevant for

the EU sales team so I'm not seeing here any USA records so with that we are providing view that protects few rows

like the orders from USA so as you can see views are really great in order to provide security to our data whether we

are protecting the columns or the rows for example in our view we can say not only I want to remove the USA orders but

let's say the department information is sensitive information and I would like to hide it from The View so you can just

simply remove it from the select and with that you are doing column level security so now I have two options that

I can provide to the users the first option doesn't have any like Ro level security it is the first view the order

details we don't have there any filters so it's going to show all the orders so here we give access only to people that

are allowed to see all data and we have another option the details with the EU it doesn't show all data it shows only a

subset that is relevant for the EU team so now it's really easy to control the security of my data using the views and

this is very important use case for the views okay so moving on to the next use case for the views we can use it in

order to have more Dynamic and flexibility in our projects so let's understand what this means if you have a

table and you have multiple users accessing this table now what can happen you might change your mind about the

design and the data model of your database where you can say you know what instead of having one table I'm going to

go and split it into two tables or maybe another decision you say you know what I'm going to go and rename a table or in

another day you decide you know what let's go and rename few columns or maybe add a column remove columns so you are

doing changes to your physical data model and you are changing stuff in the tables you know what going to happen all

those users that are accessing the tables going to scream because all of them having a complex SQL queries and

your small changes at the tables are breaking everything in their queries and what this means this means escalations

and you don't have anymore the freedom to change anything in your database without talking before to 100 people

before doing any change so we don't do that instead of that we use views so what going to happen you create a view

and you tell the users okay take this View and consume it and leave me alone and now you have again your freedom to

do any changes you want so you go to your tables and do splitting renaming and change everything you want as long

as you are updating the query between the table and the view to make sure that the users are not noticing any change so

for example if you go and split the table into two tables then you have to put in the view a join or Union in order

to reconstruct the same structure that the users are used to and if you would like to rename something in your

database like instead of ID you are now calling it a key all what you have to do now is to go to the query of the view

and rename it back from a key to an ID so no one going to notice that you are doing changes to the physical tables so

using views and offering it to users is a game changer for you because giving the users views can it gives you more

freedom Dynamic and flexibility to change anything in your data model and the tables without getting any headache

so this is amazing use case for the views okay moving on we have a lot of use cases for the views they are just

amazing so the next one is we can use views in order to introduce a second version of my data model in another

language so we could offer multiple languages to the users let's understand what this means so now we have the

following scenario we have again our table orders where the data is persisted and everything in English and of course

what happens sometimes you have like international team that are accessing your data so you have team in USA and

maybe you have team from Germany that as well are end users that want to access the data of course it depend on the

number of users that are using your database but if you have a lot of users that come from Germany and as well from

India it might make sense that you go and translate your data and the table structure into another language so for

example instead of giving access to the table orders we can create another view called bong that's the order in German

but not only you are giving a new name for the object you could go as well and rename all the columns inside the view

then the German users going to access the German View and it's going to be for them easier to understand the content of

your data phase the same thing for the Indian team and for the Indian users you can go and provide a view in Hindi I'm

not sure whether I'm pronouncing that word correct but this is the first word that I said in Hindi I don't promise

that I'm going to learn the Hindi language because it's enough to learn Germany so I'm trying as well to write

this word adish I hope it is correct and to be honest it is really interesting how you write this word in Hindi so now

back to the toic as you can see now we are using like the views in order to provide a translation for our database

by just giving a new name for the views and as well for the columns so this is another nice use case that I usually use

as well in my projects in order to provide multi languages for the data model that I have and I can do that with

the power of views now we come to my favorite use case for the views and that I personally

recommend in each project is that we can use views as a virtual data Mars in a data warehouse so now why this is my

favorite because I'm specialist in data warehouses and data leagues and this topic is very important decision in each

project like this so let's understand what this means so now a classical data warehouse architecture based on the

approach of N1 it's going to look like this we have multiple Source systems where our data are spread it and now we

would like to go and extract all of our data from these multiple sources and put it in one big database called Data

Warehouse and there will be a lot of operations on this Central database like the data going to be first cleaned and

then maybe integrated together and maybe we are building there some historical data so we're going to be doing multiple

steps in order to prepare the data for complex reporting and analyzes and what we usually do in the data warehouse we

going to store all those informations as if physical table now once we have built the data warehouse what's going to

happen we're going to have multiple use cases that would like to access the data warehouse in order maybe to do some

different reporting now it's going to be very complex if we connect immediately like a reporting engine like powerbi

directly to the data warehouse but instead of this we try to split the data warehouse into multiple subsets like we

can split it after topic or domain or departments and we call those subsets as data Mars so a data Mart is always

specific for a use case that's focus on one topic like for example we could have a dedicated Mart for the sales and

another data Mark which is dedicated only for finance topics but both of them comes from our data warehouse then the

last layer going to be like for example the reporting and dashboarding maybe you have something like powerbi where you

are creating a dashboard one data M like the sales or and as well maybe few stuff from other Marts but now the big

question here in the data Mart is how should I store the data should I store the data using tables or should I use

views and now the best practice says if you are building data Mars then use views and we call this virtual data Mars

and there are many reasons why using views at a data Mar it's way better than using tables like for example it is more

Dynamic and quicker to change them because usually at the data M you are building a lot of business Logics and

you want to have some flexibility and speed and the maintenance efforts is very simplified no need to build any

etls or data loads from the data warehouse to the data Mars and this makes the data warehouse as a real

single point of Truth for your data and once you start copying data from one layer to another layer it's going to be

really hard to maintain and chaotic and you have to have really restrict monitoring and data quality so that's

why using views you're going to always reflect the status of the data warehouse and this can to help you of course with

the data consistency which is a critical point in each data house project so there are many reasons why we build

virtual dataart and we go with the views in this layer so as you can see how the views are playing a very important role

in building a data warehouse so this is another amazing and very important use case of using views in your data

projects all right friends so now let's have a quick recap about views so we have learned that views are a virtual

table that is based on the result of a query without actually storing any data in the database so we use views in order

to persist a complex SQL logic and query in the database and we have learned that in some scenarios views are better than

CTE because it improves the reusability and reduce the complexity in multiple queries which reduce the complexity of

the whole project where the CTE only improves their usability in one query and we have learned that as well the

views in some scenarios are better than tables we have learned that they are very flexible and easier to maintain

since they don't store any data and it's really fast and easy to change stuff in the view compared to the tables but as

well we have learned that the tables are faster than views now they are like endless use cases for the views but from

my experience in projects I have chose for you the best use cases for the views the first use case is if we find like a

common repeated logic in SQL queries we can go and store this logic in view in the database so that the users don't

have to keep repeating the logic over and over so we use views in order to have a Central Business logic another

use case is to hide the complexity of your physical data model and to offer for the users an high abstracted layer

so you provide for the users something very friendly and you hide all that complex technical data model that you

have in the database because not everyone is expert with your data model when more use case we can use views in

order to implement security and to protect our sensitive data in the database so we can offer multiple views

in order to protect columns or rows in a table another use case we have learned that we can use views in order to have

more Dynamic and flexibility for your database where we offer the users a stable View and then you have the

freedom to change stuff at your physical data model without affecting all users and another nice use case for the views

we can offer multiple languages from our data model and the last use case we have learned how views play an important role

in a data warehouse system so views are amazing all right so with that we have covered everything about the views and

how to use them in your SK projects now in the next chapter we're going to learn how to use tables instead of views in

your projects if you like this video and you want me to create more content like this I'm going to really appreciate it

if you support the channel by subscribing liking sharing commenting all those stuff going to help the

Channel with the YouTube algorithm and as well my content going to reach the others so thank you so much for watching

and I will see you in the next tutorial bye

Keep this summary

Save it to LunaNotes and it becomes a real note in your library — editable, searchable, and ready to turn into flashcards or a diagram. Free to start.

Save to LunaNotes

Or summarise for another video.

This summary and transcript were automatically generated using AI with the Free YouTube Transcript Summary Tool by LunaNotes.

Related summaries

Master SQL: Comprehensive Guide to Advanced Data Analytics and Optimization

Explore an extensive SQL course covering fundamentals to advanced topics including data warehousing, analytics, complex querying, performance tuning, and AI-powered coding assistance. Learn practical techniques, real-world project workflows, and best practices to excel in data engineering and analysis using SQL.

A Comprehensive Guide to PostgreSQL: Basics, Features, and Advanced Concepts

Learn PostgreSQL fundamentals, features, and advanced techniques to enhance your database management skills.

Comprehensive SQL Course: From Basics to Advanced Database Design

Master SQL with this full beginner-friendly course covering database fundamentals, MySQL installation, table creation, data manipulation, complex querying, joins, triggers, ER diagrams, and converting ER diagrams to schemas. Learn practical examples and advanced techniques to design and manage relational databases effectively.

Introduction to Database Management Systems: Basics and Key Concepts

This video provides a foundational overview of Database Management Systems (DBMS), covering essential definitions, functionalities, and properties. Using a university database example, it explains how DBMS manages, manipulates, and shares data efficiently to meet specific organizational needs.

Understanding the ALTER Command in SQL: A Comprehensive Guide

In this video, Varun Singla explains the ALTER command in SQL, a crucial part of Data Definition Language (DDL). He covers its various functionalities, including adding, dropping, modifying, and renaming columns and tables, as well as managing constraints. The video includes practical implementation examples in Oracle, making it easy to understand the command's applications.