• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Tables and Views

 
kevin Abel
Ranch Foreman
Posts: 928
10
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm new to working with views.  

I inherited SQL having both views and tables.    

I don't know which half goes first.    Are Views created by combining data from tables or does it happen the other way?  

First SELECT data is created and then the views filter further?

Is the concept totally different and I'm not making sense?

Thanks,

Kevin
 
Paul Clapham
Marshal
Posts: 28271
95
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I looked at the documentation for MySQL, which is what I use. It says

MySQL wrote:A view can be created from many kinds of SELECT statements. It can refer to base tables or other views. It can use joins, UNION, and subqueries. The SELECT need not even refer to any tables.



So it looks like you can create a view from zero or more tables. But can you create a table from some views? I looked at the documentation for CREATE TABLE and didn't see anything about views specifically. But there is CREATE TABLE ... SELECT, which creates a new table from the result of a SELECT statement, so presumably there's nothing stopping that SELECT from selecting from a view.

For me, I've always thought of tables as being basic building blocks and views as being something more dynamic which combine tables in some way. It's probably best to start from that viewpoint. But as always it's actually more complicated than that, especially when you start using tables which are only transient objects. And no doubt there could be objects which you can ask "Should this be a table or a view" and get the answer "Whatever".
 
Stephan van Hulst
Saloon Keeper
Posts: 15649
367
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tables are where all your data are actually stored, in their most basic, normalized form.

Tables should not contain data you can derive from other sources, nor should they contain duplicate or redundant information.

Views are what their name implies: A different way of looking at the data in your tables. Maybe the normalized data in your tables is very hard to read, or maybe a lot of your application's queries use the same subquery. A view denormalizes the data into a more directly useful form.

There are two ways you can think of a view: As a special table that repeats the data in some of your other tables, just in a different form; or as a commonly used (sub)query, except it's provided by the database rather than the application.
 
kevin Abel
Ranch Foreman
Posts: 928
10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Stephan and Paul,

I'll call the non-view SQL as "ordinary".      

It seems more likely that the ordinary SQL runs first.  Then the view SQL runs.  I'm looking for the place where the ordinary SQL launches the view.

I appreciate your research and answers.

Thanks,

Kevin
 
Paul Clapham
Marshal
Posts: 28271
95
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

kevin Abel wrote:I'll call the non-view SQL as "ordinary".      

It seems more likely that the ordinary SQL runs first.  Then the view SQL runs.  I'm looking for the place where the ordinary SQL launches the view.



I think you're starting from the wrong place. There are tables and there are views. It doesn't help to invent new categories like "non-view SQL" or "ordinary".

You can create a table using CREATE TABLE, and you can create a view using CREATE VIEW. There's no SQL of any kind running those commands. Both of them result in the database containing a permanent object which contains data.

The documentation I looked at showed an example of creating a table and then creating a view from that table. I strongly suggest you look at examples online, both in documentation and tutorials. Those would be a better place to start.
 
kevin Abel
Ranch Foreman
Posts: 928
10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul,

I'm not trying to create new tables from existing tables or from a view.

Does SQL run first and collect data in memory?  Then the view takes the memory result and further filter it to a new result?

Also can it go the other way where the view runs first and puts the answer in memory.  Then SQL uses this to make a final result?

Can SQL or the view occur first?  

Maybe I still don't understand the purpose of a view.

Thanks,

Kevin
 
Stephan van Hulst
Saloon Keeper
Posts: 15649
367
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There is no "memory", and nothing is "running".

Remember, a database is just bytes on a drive.

A table is a way of structuring data.

A view is an alternative way of interpreting structured data.

If it makes your life easier, you can think of a View as a predefined SQL query that is provided by the database. This is a poor way of thinking though, since it doesn't really clarify when a query should be provided by the database, and when it should be provided by the application.
 
kevin Abel
Ranch Foreman
Posts: 928
10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Stephan,

I can tell that I became involved in more database information that I know about.  This knowledge gap happens to me often when working on different QA projects.   I am shown an application in a business area I don't know about,  a coding language to do whitebox testing,  SQL, API and all kinds of new topics.  I have to quickly learn enough to do the testing.  It used to bother me.   Now I do research and ask questions.  

I didn't know that the view is stored in the database.   I thought it was being kept in the TOAD or SQL IDE type tool.    

I'm thinking that the view is something like a Trigger that is also stored on the database.   I have seen the SQL to add a Trigger to a database but I don't know much about them either.

Thanks,

Kevin

 
Paul Clapham
Marshal
Posts: 28271
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

kevin Abel wrote:I'm thinking that the view is something like a Trigger that is also stored on the database.   I have seen the SQL to add a Trigger to a database but I don't know much about them either.



What they have in common is that they are both stored in the database. That's about it.

Triggers: Have a look at this tutorial. It's a pretty good introduction and it basically explains everything they do and why you need them to do that.
 
Tim Holloway
Saloon Keeper
Posts: 27918
198
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Stephan van Hulst wrote:
Tables should not contain data you can derive from other sources, nor should they contain duplicate or redundant information.



That's known as "normalization". In the Real World, for various (often performance-related) reasons, you may occasionally find it needful to work with un-normalized data. But if there's one lesson that IBM's OS/2 taught me it's that if you store the "same" information in multiple places, sooner or later it will bite you. The only cure for that is to make one location always be the definitive one. Better yet, normalize as much as possible.

A View is simply a shorthand for a SELECT. In its crudest form, a View would never take up any disk space in a database, because it would simply run its defining query whenever you accessed it. A SELECT with criteria would, in an efficient database, simply merge the criteria from the user's View SELECT query with the criteria from the View definition to more precisely define and optimize that particular query.

Whether it does or doesn't, and whether (and how) that stuff is cached is generally not going to be visible to you. Unless the DBA sees performance problems, in which case the DBA should be trying to tune it either directly or by suggesting reformulation of your access strategies.

There's a special kind of View known as a Materialized View which is basically a static snapshot of a SELECT done at a specific time. Unlike a regular View, it would not reflect any changes in the underlying View source(s) made since the Materialized View was created.
 
kevin Abel
Ranch Foreman
Posts: 928
10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul,

I'm looking at the link about Triggers that you recommended.  It's nice to see how this works.  I have searched on it before but it never made sense to me.

The part I'm not following is this from the link:



Where are NEW.salary and OLD.salary originating.  IS OLD and NEW some kind of built in Keyword?

THanks,

Kevin
 
Paul Clapham
Marshal
Posts: 28271
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

kevin Abel wrote:Where are NEW.salary and OLD.salary originating.  IS OLD and NEW some kind of built in Keyword?



That would be my guess. After all, some triggers will need to look at two versions of the same row, so there must be some language feature which lets them do that. But I would look at the database documentation to confirm that guess.
 
kevin Abel
Ranch Foreman
Posts: 928
10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul,

It's good to know how to use Triggers in case I run into one or need to create one.

Thanks,

Kevin
 
Paul Clapham
Marshal
Posts: 28271
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have actually created triggers in my past life. But never views. And when I read tutorials about views my usual response is "Why wouldn't you just use a regular SELECT statement?" However there may be people with better use cases than the lame examples in online tutorials. And maybe the database has optimizations which it can apply to views which don't apply to SELECT statements.
 
Tim Holloway
Saloon Keeper
Posts: 27918
198
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Clapham wrote:I have actually created triggers in my past life. But never views. And when I read tutorials about views my usual response is "Why wouldn't you just use a regular SELECT statement?" However there may be people with better use cases than the lame examples in online tutorials. And maybe the database has optimizations which it can apply to views which don't apply to SELECT statements.



It's worth noting that an auto-generated key is just another form of trigger.

But as to views, it's because somethings doing a complete SELECT from scratch would produce some really gnarly SQL. Especially if the SELECT that comprises the View contains multiple JOINs. A View is a canned simplification that avoids de-normalizing the database.

It's also sometimes useful to have Views that do something like only present, say, all transactions entered this month or all assets for a client ID. In other words, make common queries simpler. That can be useful not only from an end-user standpoint but also because it allows the DBA to optimize database access.

I've found it occasionally to define Views even for apps that are using JPA, even though JPA itself gets rids of a lot of the uglier JOIN code etc. Also, since JPA Entities work best when you pull an entire row, a View that defines only certain desired columns from a 120-column table can be a handy thing to implement. The brute-force equivalent is not ony a lot uglier, but also tends not to be type-safe, since that approach typically returns an array of Object rather than an Entity with typed properties.
 
kevin Abel
Ranch Foreman
Posts: 928
10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul and Tim,

Good information.

Thanks,

Kevin
 
reply
    Bookmark Topic Watch Topic
  • New Topic