Views

As discussed at the beginning of this chapter, views allow you to create custom viewpoints on your database that encapsulate much more complicated SQL queries. For example our subselect from the previous chapter is over 100 characters long, and therefore its purpose is not immediately obvious.

Using views, however, we can create a view that performs the exact same query and give it a memorable name like, "get_socks_people". This query can be called in the same way as you would use the normal SQL statement itself, with the added extra that you can pass more SQL clauses to it irrespective of the contents. For example, this would create a view, get_socks_people, that is functionally equivalent to our previous subselect query:

mssql_query("CREATE VIEW get_socks_people AS SELECT * FROM Customers WHERE ID IN (SELECT DISTINCT Customer FROM Orders WHERE Purchase = 'Socks')");

Note the ending semi-colon is not on the end of the statement - you may get errors if it is there. Once the view is created, it can then be queried like this:

$result = mssql_query("SELECT * FROM get_socks_people;");

The same fields (ID and Name) will be returned for use. Now, when I said that you can pass more clauses to the view irrespective of the contents, here is what I meant:

$result = mssql_query("SELECT * FROM get_socks_people WHERE Name = 'Paul';");

This time there is a WHERE clause on the end. This will be parsed properly and only one row will be returned from our data set as there is only one row with the customer name, "Paul". However, if you "decode" the view, the query that is actually being performed is this:

$result = mssql_query("SELECT * FROM Customers WHERE ID IN (SELECT DISTINCT Customer FROM Orders WHERE Purchase = 'Socks') WHERE Name = 'Paul';");

Just to make the "problem" quite clear, here is the same thing again with the subselect removed:

$result = mssql_query("SELECT * FROM Customers WHERE ID IN (foo) WHERE Name = 'Paul';");

As you can see, there are two WHERE clauses in there. If we were to write the same SQL query ourselves, it would need to be "WHERE ID IN (foo) AND Name = 'Paul';". This magic is automatically worked around by the DBMS, which means you really don't need to worry about what a view does internally - all that matters is that you know what fields will come out.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.