Views in SQL

So views, this is going to be one of the easiest things you've learned. Settle down and take it easy. Views are very easy to use.

Mostly here, you're going to learn how to use them. Technically, somebody else will have to create them. So think about when we're writing our queries.

So let's say, you know, you have queries that you've written, right? Let me go back, open up one of these files here. So, you know, you got these queries that you've written. Now, imagine if you want to run this query all the time, you want to run it every day or whatever, it doesn't matter what the query is, right? If I want to run this query every day, or if other people want to run it, we all have to have this code, right? So could I email this code to other people? Could I like put it on a server that maybe our company has like a file server or something, and people could open it up and run it every day? What if somebody went in there and like messed it up and changed it, like broke our query? You know, it's a text file, they could just say the changes accidentally, right? But also, what if I want to make sure everybody gets the update? If I'm emailing a file around, if I ever make a change, I got to email everybody the latest file.

What if they didn't get the latest version of the thing, and now they're running an older version of the query that maybe has some bug or doesn't do it the way we want? Maybe we change the product category pricing, you know, because of inflation. So it's kind of hard to manage shared queries like that. If there's a bunch of people, or even just you, you're like, I want to do this query all the time.

Imagine, what if we could save it onto the server itself, the database server, the database? What if we could store it into there, and then we could just query it right from there? And that way, if we ever update the server, if everybody's calling the one that's on the database itself, everybody gets the update. They get the latest all the time. Now, to do that, whoever creates this does need to be able to write to the server because it requires changing the server.

So in many cases, you won't be able to be the person who creates the view, but you can write the query and say to a database administrator, hey, can you save this as a view saved onto the server? And also they might also all have already created views, which are saved queries. So just think of a view as a query that you are going to view, right? You're going to view it. You're not creating it because somebody else did, or you create it and give it to somebody to save onto the server.

So think of a view as just simply like a saved query. Now, when we're querying it, we're going to query it just like a table. And so there's a couple of reasons why we might want to use a view.

And views are going to work just like we can view a table. We can go into a view and view it the same way. So one could be reusing queries.

Another thing is think about sensitive data. So for example, in the user data, when I go into the users table, notice how there's passwords here. Now those passwords are encrypted, but still I'm seeing something about the password.

What about if there were birthdates or social security numbers that have to be stored? Do you just want everybody to be able to go and look at everybody's social and birthdate and all that sort of really sensitive financial detail? You're going to open yourself up to lawsuits that just anybody can go in and view everybody's. That's just too tempting for people to steal people's identity. So that's just an accident waiting to happen.

So not only should you not be able to just go in and change data normally for the vast majority of people, but most people shouldn't even be able to view the entire database. We want to limit what they can see. So another thing is because a view, which is just a saved query, when I write a query, I could just choose which columns to show people.

I could just not show you certain columns. They're in my database. I could just choose to not show them to you because I could create a list of the columns I do want you to see and I could leave off the ones I don't want you to see.

And you would have no idea because if I don't give you access to the original table and I only give you access to the view, you're going to query the view and you're going to think that's all the data you have. You don't know what you're missing. So for sensitive data, we create views.

For saving queries, we create views. And when we're sharing those queries, let's say other people don't know how to write those queries. It might be a big long query that we have, right? They don't need to worry about all that.

It's going to be very easy for them to use the view. It's going to be very easy for us to use the view. So views have been here all along.

You just haven't even noticed that they've been right there under tables because we haven't gone into that folder. So views, in this case, these are views that we created for you. Also, here when I am going to, I'm going to open up the file for this here for views.

I'm going to choose my database up here. When we've been doing this and we said select everything from, you probably didn't even really notice this too much. These are views.

They've been right there all along. We've just been ignoring them. We've only been paying attention to the thing that we wanted, which was the tables.

But they're all on. We created some views here for you. And just like I can go into a table and double click on it and view the stuff in it, if I go to a view, double click on it, I can see this stuff in it as well.

It kind of looks like a table to me. I can open it up and I can see the columns that are in it. It looks and kind of acts in every way, just like a table.

And if I say to select everything from a view, it queries just like a table. It's just not a real table in the database. We created it by creating a query and saving it as a view.

So behind the scenes, think of it as like a virtual table. It's not a real thing. It's a, the closest thing is actually, it's almost like a sub query.

You query it and it queries the database. So you're kind of one step removed. So the view queries the actual database.

You query the view and the view queries the database. So it's all up-to-date information. It doesn't like store any data in the view.

It's not like duplicating data. It doesn't add file size to the database or anything. But all of that stuff equates to something behind the scenes in the real database.

You can use any features you want inside of that query that creates the view. So you can do anything. You can use a view, just like you would a table.

So you can use them in joins. You can do whatever you want. So views are great for security, for reusing queries.

The only downside to it is technically speaking, you are querying a query. So it could be slightly slower than querying the tables directly if you have access. But that is the small price that we pay.

I'm not saying they're slow. They're just slower than if you query the tables directly. So most of the time, you will not be able to create a view, but just so you can see how they're created, you just write a select statement of anything that you want.

And above it, you say create view, give it a name as. And so basically, this is saying like save a view with this name that uses this code. So anybody could write a query and say, hey, let me save it to the server if you have write privileges.

If you don't have write privileges, you'd have to give it to a database administrator who does, and they would have to approve the saving of it. And then they could put onto the server for you. They run the code like this.

So you probably can't do this, but I just want you to see that you're really just saving a query to the server. Or if you are using a view that somebody else made, that's what's going on behind the scenes. How do we use it? Any place you put in a table, you can put in a view.

Just think of a view as just another table. Acts and works in every single way like a table. We could have run this whole class off of views instead of tables, and they would have worked exactly the same.

They would not have changed anything about our queries. Functionally, they would have been identical. So instead of from accepting a table, from can accept a view in exactly the same way with no other change.

So there are also another kind of view, by the way, in Postgres only. So there's a regular view and what's called a materialized view. Materialized views create what's called a cache or a temporary memory where they do actually store the data as like an optimized version of the data to make them perform faster.

So a normal, just plain view queries the live database, and it's always up to date. It never needs to be updated manually. Materialized views, to be faster, they have to create cache.

The downside of that is that cache is created once, and it doesn't get updated until you manually recreate the cache. Now, for some people, that's fine. So remember the example that I gave, I think it was yesterday, where there's a company who gets database dumps at the end of every day from Amazon, Spotify, Apple Music.

They only get that data once a day. They get it dumped into their database. So let's say that they create views.

They would create a materialized view for faster performance. They don't need it updating live throughout the day because their data is not changing throughout the day. At the end of the day, they get a database dump, and they update the view.

Next day, database dump, update the view. That way, they get the performance benefits of the materialized view. So what we have to know is that there are two places to look, the views and materialized views.

How you use both are exactly like you use a table. It's just if I only taught you about tables, you would be missing out on views and materialized views because you never would have looked there. If you think your only source of data is tables, you're potentially missing out on opportunities to use your data.

On your server, if people created them, there might be views, and there might be materialized views. Again, how we use them is exactly the same. There is a performance difference, but in this one, this will be cached, but somebody would need to update that cache.

We don't do any of that. That's for database administrators. For us, when I say select everything from, I could say the user's table, and I get the whole user's table.

If I say the user's view, and it doesn't have to be named with view, by the way. I did it just to be clear that we understand which one we're querying. And if I query that, I will get whatever that view gives me.

Notice how passwords is not there because when I created that query, when I created that view, I said select, and I put in all the columns except password. I went in here to all my columns here, and no, that one doesn't work here. If I grabbed all those, here's a little cool trick.

Drag and drop. Let's say I don't want this one. I can command click it, and I can drag and drop all those over here and see how it types all those out for you.

Do you know how many people never think about dragging and dropping? They think about typing it all out. And then I can just put commas between them. Comma, comma, comma, chameleon.

That joke there. So you can put commas. So I just did that to do all those from users, and I just left off password.

And then I saved that as a view. That's what creates this view. And I also saved it once as a regular view and once as a materialized view.

I gave them different names just so we can know which one worked great. To us, there really won't be a performance difference here because it's such a small query, but given enough data, this would work faster than this. Normally, you're not going to be given both.

It's going to be one or the other. I'm just doing it for the sake of demonstration. So from us as a perspective, how do we think about using these six? Just use them like a table.

If you don't know what's in them, double click them just like you do a table, and you can see what's in it. It's like it just ran that query for you. So just think of it as another source of data that if I didn't teach you about it, I would be limiting what you have.

Now, I can't guarantee that you're going to have either of these because someone had to create them on the server. Maybe they did. I don't know what's going to be in there.

It might be simple queries behind the scenes, or it might be a much more advanced query behind the scenes. But to you, it doesn't matter. You just query it like a regular table to see what's inside of it.

And then you could do whatever else you want with it as well. So for example, if I want to see what's inside here, I can run this query, and I can see that there are states, and maybe I want to see only people that are from a user state of whatever. Maybe I want to see New Yorkers.

There's all my New Yorkers. Looks and feels just like a regular table, right? That's why I said it's going to be easy because you're just learning another place to get tables from, essentially. They just act like virtual tables.

Everything you know about tables, materialized views and views work the same way. It's just important to know because you're given pre-made things. And sometimes with the users, we're using it as a way to give people information without sharing the sensitive stuff like the passwords.

Other times, the total spent per user here. If I'm doing that one, and this was showing that you could limit things, you could do whatever. That's the materialized view that I already showed.

But let's say it's this one here. I'll let you do it because we got some challenges there. But let's say you want to see what's in here.

You could double click. You could see what's in here. Now, if you're viewing only Gmail users, because this is the Gmail only, see how we got users that are on the Gmail address, you could imagine what somebody might have wrote as a query behind the scenes.

They might have said, hey, show me users and then where the email is like Gmail. But I just made a view here where anybody can do that. And they don't have to know how to write that query.

So you could create a query that behind the scenes does something very complicated or it's very simple, but you're just doing it in a way like if you always need to be doing this, you could just open it up and you've got all that data there without having to know how to create. You could do complex window functions or group bys and then save it onto the server so that everybody can benefit from that without having to share files. And then if we ever update it on the server, it updates forever.

So the takeaway is when you're browsing your data, don't just look at your tables. Always look at your views to see if there's any views in both kinds of views. And if you want to query them, just query them like a table.

They act just like a table. You can filter with where's, you can do order bys, you can do joins, you can do anything you want. They're just like a table.

That's why they're easy to use because you just, they're a drop in replacement for a table. Also, another thing I forgot to mention too about views is sometimes for external applications, we create views to limit what we're sharing with external applications. So for example, if you're connecting into Tableau or Power BI, you might not want them going in and getting direct access to tables with all of the sensitive information.

So you might want to limit and create a view so that you control how much data they get in their data visualization app, because you don't want to share the whole database and let them visualize anything, including social security numbers and birthdates and that sort of stuff. We can give them a view. And then a lot of times those external applications can connect into the view instead of connecting into the whole database as well.

So that's the other side of things. Yes, we can use them in SQL, but we can also use them outside of SQL, outside the database in other third-party applications. So CRMs, data visualization apps, other third parties.

So the only thing is you can't create them unless you can write to the server. So only a person with write privileges can do it. If you ever want something to be on the server, you could write the query and they have to pass it on to somebody and say, hey, could we save this onto the server as a view? And they'd have to approve that of course, and then they can save it onto there.

If they think that's something that everybody could benefit from, they would choose to put it onto the server. So that would be the process. You write the query, give it to them, and then they could put it on the server.

All right. So let's take a few minutes and open this one up. This is, we're jumping ahead to 2.1 views.

And then we'll come back and we'll do the string functions.