Structured Query Language (SQL) is used to access a database, but is not always used to it's fullest potential. Also, frequently you come across an error in a statement that looks like it "should" work, but doesn't. How often have you used a column alias in a SQL statement, only to see this error:
Invalid column name 'myAlias'.
This can typically happen when you try to somehow manipulate one or more columns and then give the column an alias. When referencing that alias within your SQL statement is where you will run into problems. The following query shows the problem:
Queries used in these examples are from the Northwind database, which ships with MS Access and MS SQL Server. A MySQL version can be found at http://www.flash-remoting.com/examples/frdg/northwindmysql.zip
SELECT LEFT(ProductName, 5) as TruncatedProduct
FROM Products
This query returns the first five characters of the ProductName field and runs fine in most environments. It is only when you attempt to use the manipulated column alias in a WHERE clause that you will see an error:
SELECT LEFT(ProductName, 5) as TruncatedProduct
FROM Products
WHERE TruncatedProduct LIKE 'ch%'
You'll get this error in SQL Server, and similar errors in other RDBMS systems:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'TruncatedProduct'.
In SQL Server or Access, you can avoid the problem by putting your original query into a view, and then call the view when you want to use a WHERE clause:
CREATE VIEW vwGetTruncatedProduct
AS
SELECT LEFT(ProductName, 5) as TruncatedProduct
FROM Products
Call the view like this:
SELECT * FROM vwGetTruncatedProduct
WHERE TruncatedProduct LIKE 'ch%'
Now there are no errors. It is not always possible to create views, however. Also, some systems like MySQL do not have the availability of views. Also, it would be nice to be able to do the query in one step.
Another solution is to use the same expression in your WHERE clause that you used to create the alias:
SELECT LEFT(ProductName, 5) as TruncatedProduct
FROM Products
WHERE LEFT(ProductName, 5) LIKE 'ch%'
This works fine as well, but becomes cumbersome when the column manipulation becomes more involved.
Why would a query like this be needed? Well, there are many times when you will need to create a search string out of several columns, for example:
SELECT ProductName + CompanyName + CategoryName as SearchField
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
WHERE SearchField LIKE '%meat%'
This will cause the error as well. Or you might need to manipulate several columns to provide one simple value to do a match against. The solution follows.
Table aliases are nice, not only for simplifying your SQL to make it more readable (see the article Writing Readable SQL), but also for creating virtual tables on the fly. When we create a virtual table like this, we can write SELECT statements against it. Take the first query we looked at. We can make an alias name of MyTemp to the original statement, write a SELECT statement against it, and use a WHERE clause on the field alias that we were previously unable to do:
SELECT * FROM
(SELECT LEFT(ProductName, 5) as TruncatedProduct
FROM Products) MyTemp
WHERE MyTemp.TruncatedProduct LIKE 'ch%'
Notice the original query (highlighted) is enclosed in parenthesis and given the alias MyTemp. A simple SELECT * is used to select everything from the enclosed query. Normally we don't use SELECT * under any circumstance when creating queries, but in this case the inner query already creates the resultset using very specific criteria. The resulting virtual table can be selected in its entirety because it is already a small subset of the Products table.
This query will now run in one step on most RDBMS systems, including MySQL versions 4.1 and later. MySQL versions before 4.1 do not support subqueries, another serious limitation of that RDBMS.
The second example query will look like this:
SELECT * FROM
(SELECT ProductName + CompanyName + CategoryName as SearchField
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID) MyTemp
WHERE MyTemp.SearchField LIKE '%meat%'
SQL is a robust language with many intricacies. A thorough understanding of the language can often provide you with workarounds to common problems.