How to Use SQL Column References

By Allen G. Taylor

Every column contains one value for each row of a table. SQL statements often refer to such values. A fully qualified column reference consists of the table name, a period, and then the column name (for example, PRICING.Product). Consider the following statement:

SELECT PRICING.Cost
 FROM PRICING
 WHERE PRICING.Product = 'F-35' ;

Here PRICING.Product is a column reference. This reference contains the value ‘F-35’. PRICING.Cost is also a column reference, but you don’t know its value until the preceding SELECT statement executes.

Because it only makes sense to reference columns in the current table, you don’t generally need to use fully qualified column references. The following statement, for example, is equivalent to the previous one:

SELECT Cost
 FROM PRICING
 WHERE Product = 'F-35' ;

Sometimes you may be dealing with more than one table — say, when two tables in a database contain one or more columns with the same name. In such a case, you must fully qualify column references for those columns to guarantee that you get the column you want.

For example, suppose that your company maintains facilities in both Kingston and Jefferson, and you maintain separate employee records for each site. You name the Kingston employee table EMP_KINGSTON, and you name the Jefferson employee table EMP_JEFFERSON. You want a list of employees who work at both sites, so you need to find the employees whose names appear in both tables. The following SELECT statement gives you what you want:

SELECT EMP_KINGSTON.FirstName, EMP_KINGSTON.LastName
 FROM EMP_KINGSTON, EMP_JEFFERSON
 WHERE EMP_KINGSTON.EmpID = EMP_JEFFERSON.EmpID ;

Because each employee’s ID number is unique and remains the same regardless of the work site, you can use this ID as a link between the two tables. This retrieval returns only the names of employees who appear in both tables.