Simplifying table names in SQL

Using SQL Table Short Names for Simplicity

I am a big believer in having meaningful names for your SQL tables.  It is much more complete, and self documenting, to have a table name such as INVOICE_HEADER, than, say, I_H.

Still, when typing your queries out, long names can get tiresome.  Fortunately, there is a way to reference a table by a shorter name on-the-fly.

Using the invoice example, let’s say that we have the tables INVOICE_HEADER and INVOICE_LINES.  Both tables contain a field for the invoice number, which is the key we want to use.  The field is named INVOICE_NO.  The header also contains a date field (INVOICE_DATE), and the line table also contains a line number (LINE_NO) and item number (ITEM_NO).

So, if we wanted to select these fields, the syntax would be:
SELECT INVOICE_HEADER.INVOICE_NO,
       INVOICE_HEADER.INVOICE_DAT,
       INVOICE_LINES.LINE_NO,
       INVOICE_LINES.ITEM_NO
  from INVOICE_HEADER
  inner join INVOICE_LINES on INVOICE_LINES.INVOICE_NO=INVOICE_HEADER.INVOICE_NO

As you can see, it gets very tiresome typing INVOICE_HEADER and INVOICE_LINES all the time.  It would be much easier for the duration of this query to refer to the header table as "H" and the lines table as "L".  SQL has an easy way to do this.

At the point that the table is defined, in the above case that is the "from" and "inner join" clauses, the table name can be changed for the purposes of the SQL statement.  This is done by simply adding the name that you want to refer to the table with, immediately after specifying the table.  In the rest of the statement, the short name is used instead of the long one.

So, the following would be used to simplify our previous statement:
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

That is much easier to type.  The header table is now "H", and the line table is now "L".  This is for the purposes of the specific SQL statement only.  The table name in the database remains unchanged.

 

Leave a Reply