The FROM Clause

By Bill Graziano on 24 June 2002 | Tags: SELECT

I was digging through Books Online the other day and I ended up on the FROM clause. I was expecting a simple little entry but that wasn't quite what I found ...

The most common use for a FROM clause is in a SELECT statement. Something like:

SELECT *
FROM authors

This tells SQL Server to SELECT all the records from the authors table. It's the first option Books Online lists for the FROM clause.:

[ FROM { < table_source > } [ ,...n ] ] 
< table_source > ::= 
    table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ] 
    | view_name [ [ AS ] table_alias ] [ WITH ( < view_hint > [ ,...n ] ) ] 
    | rowset_function [ [ AS ] table_alias ] 
    | user_defined_function [ [ AS ] table_alias ]
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
    | < joined_table >

The Books Online entry goes on to define join types but this article isn't going to cover those.

Views and Table Hints

The second option is a view. This is usage is the same as a table:

SELECT *
FROM titleview

titleview is a view in the pubs database. You can also give each table an alias. This is most usefull is situations where you're joining tables and column names are identical across tables:

select	a.au_id, au_lname, t.title_id, title
from	authors a
join	titleauthor ta ON ta.au_id = a.au_id
join	titles t ON t.title_id = ta.title_id

The authors table is give an alias of a. You can now reference any column from the authors table with an a. prefix such as a.au_id. It's much easier than typing the full table name each time. The AS keyword is optional in defining table aliases.

You can also provide table hints for each table. Table hints can specify indexes, lock tables and define how to handle locked rows. For example to hold an exclusive table lock for the duration of the statement or transaction, you can run the following statement:

select *
from authors WITH (TABLOCKX)

There are quite a few additional table hints you can use. You'll have to read Books Online for all of them. Please be carefull using these especially the ones that specify an index. SQL Server usually does a great job determining query plans. Only use table hints if you're trying to solve a particular problem.

Rowset Functions

You can also use a rowset_function in a FROM clause. These are CONTAINSTABLE, FREETEXTTABLE, OPENDATASOURCE, OPENQUERY, OPENROWSET and OPENXML. These functions return a table that a SELECT statement can use.

Select	[KEY], RANK, LastName
from	ContainsTable(Employees, *, 'french or university') as S,
	Employees as E
Where	E.EmployeeID = S.[KEY]
Order by Rank desc

The CONTAINSTABLE table function is used in full-text indexing. We can treat the results of this function just like a table and join it to the Employees table. You'll also noticed I used the AS keyword to define a table alias in this case.

User Defined Functions

You can also use a user defined function in a FROM clause. Consider the following function:

Create function GetAuthors ( @State char(2) )
RETURNS TABLE
AS
RETURN Select *
  from authors
  where state = @state

A simple way to use this function is:

SELECT *
FROM GetAuthors ('CA')

There's a more complicated version of the CREATE FUNCTION statement that allows you to perform some processing inside the function. This basically gives you the ability to run code inside a SELECT statement. I'm planning to write a article on this in the future.

Derived Tables

A derived table just replaces a table with a SELECT statement. For example,

SELECT au_id, au_lname
FROM (select *
      from authors
      where state = 'ca') a

will return all the authors from California. A derived table always needs an alias. My example of this is pretty simple. Garth has a better example in his article Using Derived Tables to Calculate Aggregate Values Derived tables are very handy when you deal with GROUP BY's as Garth's article shows.

That's it for the FROM clause. Remember these will also work when you use the FROM clause in a DELETE or UPDATE statement.