FROM

Earlier I introduced you to the FROM clause, which specifies the tables from which to retrieve the rows that satisfy the SELECT query. As discussed earlier, the FROM clause is not required when the select list contains only constants, variables, and arithmetic expressions. In other words, as soon as you specify column names, you are implicating tables, and the SELECT does not know which tables unless you list them in the FROM clause. But FROM is a lot more than a subordinate clause that specifies tables name, as we will soon discover.

The FROM clause requires a source, or more specifically a table source. The table source can be tables, views, derived tables, the results of joins, and rowset functions. The base syntax for the table source is as follows:

 table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ] | view_name [ [ AS ] table_alias ] | rowset_function [ [ AS ] table_alias ] | derived_table [ AS ] table_alias [ (column_alias [ ,...n ] ) ] | joined_table 

The table_name placeholder specifies to the FROM clause the name of a table or view, or a table derived from the rowset function. Using the AS directive, you can also come up with an alias to specify as the optional alias name of the table. You can also alias the name of a view. The following example specifies an alias for the base table:

 SELECT Customers FROM Cust AS "Customers"...

At first sight, the alias for a table name specified in the FROM clause seems redundant. It is in fact redundant in simple expressions, but it will make sense when you need to do more with the table than just searching it, as you will soon discover.

The FROM keyword is needed whenever you are referencing tables, views, derived tables, joined tables, and table variables in a SELECT statement. (See Chapter 2 for a primer on tables and views in SQL Server 2005. Creating them in T-SQL or interactively is covered in Chapter 10.) In this section, we will only use FROM to specify base tables as follows:

 SELECT * FROM table_name 

In the preceding example, the statement makes use of the * (star) operator, which instructs the query to return all rows and columns from the table. You can also specify more than one table in the query as follows:

 SELECT * FROM table_name1, table_name2, table_name3 

T-SQL lets you specify up to 256 source tables after the FROM clause. If a table or view exists in another database, other than the one you are attached to, or even another server, you must provide a fully qualified table name (FQTN) database.owner.object_name after the FROM keyword.

Caution 

The fully qualified table name does not guarantee you can still connect and run the query, because permissions apply. (See Chapter 5.)

As an example, the statement

 SELECT * FROM Agents

returns the following result set from a two-column table, which represents all rows and all columns:

Agent Name	Agent Nickname
A1	Captain
A2	Gish
A3	Kimlet
A4	Frog
A5	Lofty
A6	Hota

Using Derived Tables for the Table Source

Derived tables are virtual tables that you can construct on the fly using subqueries in the FROM clause. You can then use the derived table as a table source to query against. The following statement suggests a simple subquery that produces a derived table:

 SELECT 'Fruit Cake Sales' = SUM(Qty) from (select Qty FROM [order details] where product ID = 5) Orders

The result is identical for this query as in the earlier statement for fruit cake sales; however, after the subquery has executed, a derived table is created called Orders (an alias), which becomes the de facto table source. This is a very useful feature because you can create a derived table from several different base tables or from some construction that does not use a base table at all. Derived tables can also be concocted in WHERE clauses as a means of providing keys for search conditions and other useful stuff. And speaking of searches ....