Using views in SQL

Using views in SQL

There are many times that the information from multiple tables needs to be viewed together.  While I have previously outlined how to do this using joins, if the information is frequently referenced, it is advantageous to use a view.

Views can be thought of as "logical tables".  By that, they act like a table, in that you can query them, use them in joins, and so forth.  However, they do not actually exist as a table, but rather the information is assembled when the view is referenced.

For example, let us use the idea of invoice header, and line file, again.  As previously discussed, using the following join
     SELECT H.INVOICE_NO,
            H.INVOICE_DAT,
            L.LINE_NO,
            L.ITEM_NO
       from INVOICE_HEADER H
      inner join INVOICE_LINES L on L.INVOICE_NO=H.INVOICE_NO
will generate rows containing invoice number, date, line number, and item number.

To create a view for the above example, the following would be done:
     CREATE VIEW USER_VIEW_INVOICES as
     SELECT H.INVOICE_NO,
            H.INVOICE_DAT,
            L.LINE_NO,
            L.ITEM_NO
       from INVOICE_HEADER H
      inner join INVOICE_LINES L on L.INVOICE_NO=H.INVOICE_NO

Now, to use the view, simply reference it just as you would a table.  For example
     SELECT * FROM USER_VIEW_INVOICES
will return all rows resulting from the SELECT/JOIN statement in the view.

I find this most useful for performing remote queries, such as for pivot tables, since you do not have to worry about joining all of the tables in your remote query.  Also, when working with very long and complex joins, I find it more convenient to break them into their logical pieces, and create views for those pieces.  Then, join these different views, to get the final result.

Dave
 

Leave a Reply