A discussion of joins would be incomplete without discussing a shorthand join notation called aliasing. In "Joins 101," October 2001, I used both the ANSI-recommended and older-style T-SQL to answer the question, Who placed an order and what product did they purchase? (To create the sample TSQLTutorJoins database I use, you can use the script in Web Listing A.)
Learn more at "n-Table Joins and Search Conditions" and
Using the recommended ANSI syntax, the complete JOIN statement is
SELECT Customer.FirstName
AS CustomerFirstName,
\[Order\].ProductID
FROM dbo.Customer
INNER JOIN dbo.\[Order\]
ON Customer.CustomerID =
\[Order\].CustomerID
I qualified all columns in this statement with their appropriate table names for readability. The more you work with databases, the more often you'll have to look at someone else's code (or code you wrote years ago) that uses joins of more than two tables. The more tables that are being joined, the more difficulty you'll have figuring out which table each column comes from. By qualifying each column with the table name, you make your code much more readable to everyone who needs access to it—including you.
So, when is qualifying the table name required? If a column name referenced in the query appears in more than one table, it is said to be ambiguous unless qualified. To remove the ambiguity, you must qualify the column name with the table name. So you could rewrite the previous query with only the ambiguous column names qualified:
SELECT FirstName
AS CustomerFirstName,
ProductID
FROM dbo.Customer
INNER JOIN dbo.\[Order\]
ON Customer.CustomerID =
\[Order\].CustomerID
Still, you can shorten this syntax while making it more readable. By using table aliases, you can replace the fully qualified table name throughout the query with an alias name instead. An alias in this context is an alternative name or reference that you define within a T-SQL statement for use within that statement. SQL Server doesn't store the alias permanently. Usually, an alias is one or two characters long (generally much shorter than the actual table name), but that's not a requirement. When you're using aliases, I recommend that you use a one- or two-character alias that somehow reflects the table name. For this query, let's use the alias C for Customer and the alias O for Order. Qualifying all columns with the alias names and using the recommended ANSI syntax, the complete JOIN statement is
SELECT C.Firstname
AS CustomerFirstName,
O.ProductID
FROM dbo.Customer AS C
INNER JOIN dbo.\[Order\] AS O
ON C.CustomerID =
O.CustomerID
You can use aliases with any ANSI-standard syntax. A common practice with the old T-SQL style is to remove the AS from the FROM line. The previous query is the preferred syntax, but the following statement will execute:
SELECT C.Firstname CustomerFirstName,
O.ProductID
FROM dbo.Customer C
INNER JOIN dbo.\[Order\] O
ON C.CustomerID =
O.CustomerID
AS isn't required in any part of the statement, but in addition to being ANSI standard, it helps readability.
Although aliases aren't permanent, you can create a view with a shorter name and use it instead. For example, you can create a view of the Customers table and name it C.
CREATE VIEW C AS SELECT * FROM dbo.Customers
In each query, you can now reference C.
Views are an excellent way of creating an alternative look at the data, but in this case, they only simplify syntax and add complexity in administration. I prefer the more temporary version of aliasing: using simple, short, alternative names created only for use within the query. You'll find aliases helpful as you write joins, especially as we continue the discussion about cross joins, self joins, and outer joins.
To learn more, see "APPLY and Reuse of Column Aliases."
