The From Clause and SQL Queries - Views

A view is a query that is stored in the data dictionary. It looks and acts like a table, but there is no data associated with a view (this is why I call it a virtual table). When you issue a query against a view, your query is merged with the view definition to create a final query to be executed.

To demonstrate, here’s a view definition that queries theemployeetable and includes a call to a built-in function:

  CREATE VIEW employee_vw AS
  SELECT emp_id, fname, lname,
    YEAR(start_date) start_year
  FROM employee;

After the view has been created, no additional data is created: theselectstatement is simply stored by the server for future use. Now that the view exists, you can issue queries against it, as in:

  SELECT emp_id, start_year
  FROM employee_vw;

   emp_id  start_year
  -------- ----------
        1        2001
        2        2002
        3        2000
        4        2002
        5        2003
        6        2004
        7        2004
        8        2002
        9        2002
       10        2002
       11        2000
       12        2003
       13        2000
       14        2002
       15        2003
       16        2001
       17        2002
       18        2002

Views are created for various reasons, including to hide columns from users and to simplify complex database designs.

Views are not supported in MySQL until Version 5.0.1. They are, however, a heavily used feature of the other database servers, so, for those readers planning to work with MySQL, you should keep them in mind.

Because Version 4.1.11 of MySQL does not include views, I intentionally left out the mysql> prompt in the previous query along with the usual result-set formatting. I use this same convention in several other chapters in the book when I show a SQL feature not yet implemented by MySQL.

Next: Table Links >>
 

More Database Articles Articles
More By O'Reilly Media