FROM
Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements.
Syntax
[ 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 >
< joined_table > ::=
< table_source > < join_type > < table_source > ON < search_condition >
| < table_source > CROSS JOIN < table_source >
| [ ( ] < joined_table > [ ) ]
< join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [ OUTER] } ]
[ < join_hint > ]
JOIN
Arguments
<table_source>
Specifies a table or view, both with or without an alias, to use in the Transact-SQL statement. A maximum of 256 tables can be used in the statement. A table variable may be specified as a table source.
If the table or view exists in another database on the same computer running Microsoft® SQL Server™, use a fully qualified name in the form database.owner.object_name. If the table or view exists outside the local server on a linked server, use a four-part name in the form linked_server.catalog.schema.object. A four-part table (or view) name constructed using the OPENDATASOURCE function as the server part of the name also may be used to specify the table source. For more information about the function, see OPENDATASOURCE.
table_name
Is the name of a table. The order of the tables and views after the FROM keyword does not affect the result set returned. Errors are reported when duplicate names appear in the FROM clause.
[AS] table_alias
Is an alias for table_name, view_name, or rowset_function, used either for convenience or to distinguish a table or view in a self-join or subquery. An alias is often a shortened table name used to refer to specific columns of the tables in a join. If the same column name exists in more than one table in the join, SQL Server requires that the column name must be qualified by a table name or alias. (The table name cannot be used if an alias is defined).
WITH ( < table_hint > )
Specifies a table scan, one or more indexes to be used by the query optimizer, or a locking method to be used by the query optimizer with this table and for this statement. For more information, see Table Hints.
view_name
Is the name of a view. A view is a "virtual table", usually created as a subset of columns from one or more tables.
WITH ( < view_hint > )
Specifies a scan of the indexed view. By default, the view is expanded before the query optimizer processes the query. View hints are allowed only in SELECT statements, and cannot be used in UPDATE, DELETE, and INSERT statements.
rowset_function
Specifies one of the rowset functions, which return an object that can be used in place of a table reference. For more information about a list of rowset functions, see Rowset Functions.
user_defined_function
Specifies a user-defined function that returns a table. If the user-defined function is a built-in user-defined function, it must be preceded by two colons, as in
FROM ::fn_listextendedproperty
derived_table
Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query.
column_alias
Is an optional alias to replace a column name in the result set. Include one column alias for each column in the select list, and enclose the entire list of column aliases in parentheses.
< joined_table >
Is a result set that is the product of two or more tables, for example:
SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1 = tab4.c1
ON tab2.c3 = tab4.c3
For multiple CROSS joins, use parentheses to change the natural order of the joins.
< join_type >
Specifies the type of join operation.
INNER
Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.
FULL [OUTER]
Specifies that a row from either the left or right table that does not meet the join condition is included in the result set, and output columns that correspond to the other table are set to NULL. This is in addition to all rows usually returned by the INNER JOIN.
Note It is possible to specify outer joins as specified here or by using the old nonstandard *= and =* operators in the WHERE clause. The two methods cannot both be used in the same statement.
LEFT [OUTER]
Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.
RIGHT [OUTER]
Specifies all rows from the right table not meeting the join condition are included in the result set, and output columns that correspond to the other table are set to NULL, in addition to all rows returned by the inner join.
<join_hint>
Specifies that the SQL Server query optimizer use one join hint, or execution algorithm, per join specified in the query FROM clause. For more information, see Join Hints later in this topic.
JOIN
Indicates that the specified join operation should take place between the given tables or views.
ON <search_condition>
Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are often used, for example:
SELECT ProductID, Suppliers.SupplierID
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
When the condition specifies columns, the columns do not have to have the same name or same data type; however, if the data types are not identical, they must be either compatible or types that Microsoft® SQL Server™ can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type using the CAST function.
There may be predicates involving only one of the joined tables in the ON clause. Such predicates also may be in the WHERE clause in the query. Although the placement of such predicates does not make a difference in the case of INNER joins, they may cause a different result if OUTER joins are involved. This is because the predicates in the ON clause are applied to the table prior to the join, while the WHERE clause is semantically applied on the result of the join.
For more information about search conditions and predicates, see Search Condition.
CROSS JOIN
Specifies the cross-product of two tables. Returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join.
Table Hints
A table hint specifies a table scan, one or more indexes to be used by the query optimizer, or a locking method to be used by the query optimizer with this table and for this SELECT. Although this is an option, the query optimizer can usually pick the best optimization method without hints being specified.
Caution Because the query optimizer of SQL Server usually selects the best execution plan for a query, it is recommended that <join_hint>, <query_hint>, <table_hint>, and <view_hint> only be used as a last resort by experienced developers and database administrators.
The table hints are ignored if the table is not accessed by the query plan. This may be a result of the optimizer's choice not to access the table at all, or because an indexed view is accessed instead. In the latter case, the use of an indexed view may be prevented by using the OPTION (EXPAND VIEWS) query hint.
The use of commas between table hints is optional but encouraged. Separation of hints by spaces rather than commas is supported for backward compatibility.
The use of the WITH keyword is encouraged, although it is not currently required. In future releases of SQL Server, WITH may be a required keyword.
In SQL Server 2000, all lock hints are propagated to all the base tables and views that are referenced in a view. In addition, SQL Server performs the corresponding lock consistency checks.
If a table (including system tables) contains computed columns and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables (the table hints are not propagated). For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions (accessing columns in another table). The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.
SQL Server does not allow more than one table hint from each of the following groups for each table in the FROM clause:
- Granularity hints: PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, or TABLOCKX.
- Isolation level hints: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.
The NOLOCK, READUNCOMMITTED, and READPAST table hints are not allowed for tables that are targets of delete, insert, or update operations.
Syntax
< table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
Arguments
INDEX ( index_val [ ,...n ] )
Specifies the name or ID of the indexes to be used by SQL Server when processing the statement. Only one index hint per table can be specified.
If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.
The alternative INDEX = syntax (which specifies a single index hint) is supported only for backward compatibility.
If multiple indexes are used in the single hint list, the duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the table. The order of the indexes in the index hint is significant. A multiple index hint also enforces index ANDing and SQL Server applies as many conditions as possible on each index accessed. If the collection of hinted indexes is not covering, a fetch is performed after retrieving all the indexed columns.
Note If an index hint referring to multiple indexes is used on the
The maximum number of indexes in the table hint is 250 nonclustered indexes.
FASTFIRSTROW
Equivalent to OPTION (FAST 1). For more information, see FAST in the OPTION clause in SELECT.
HOLDLOCK
Equivalent to SERIALIZABLE. (For more information, see SERIALIZABLE later in this topic.) The HOLDLOCK option applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement in which it is used. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.
NOLOCK
Equivalent to READUNCOMMITTED. For more information, see READUNCOMMITTED later in this topic.
PAGLOCK
Takes shared page locks where a single shared table lock is normally taken.
READCOMMITTED
Specifies that a scan is performed with the same locking semantics as a transaction running at READ COMMITTED isolation level. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL.
READPAST
Specifies that locked rows are skipped (read past). For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. READPAST applies only to transactions operating at READ COMMITTED isolation and reads past only row-level locks. This lock hint is used primarily to implement a work queue on a SQL Server table.
READUNCOMMITTED
Specifies that dirty reads are allowed. This means that no shared locks are issued and no exclusive locks are honored. Allowing dirty reads can result in higher concurrency, but at the cost of lower consistency. If READUNCOMMITTED is specified, it is possible to read an uncommitted transaction or to read a set of pages rolled back in the middle of the read; therefore, error messages may result. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL.
Note If you receive the error message 601 when READUNCOMMITTED is specified, resolve it as you would a deadlock error (1205), and retry your statement.
REPEATABLEREAD
Specifies that a scan is performed with the same locking semantics as a transaction running at REPEATABLE READ isolation level. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL.
ROWLOCK
Specifies that a shared row lock is taken when a single shared page or table lock is normally taken.
SERIALIZABLE
Equivalent to HOLDLOCK. Makes shared locks more restrictive by holding them until the completion of a transaction (instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether or not the transaction has been completed). The scan is performed with the same semantics as a transaction running at the SERIALIZABLE isolation level. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL.
TABLOCK
Specifies that a shared lock is taken on the table held until the end-of-statement. If HOLDLOCK is also specified, the shared table lock is held until the end of the transaction.
TABLOCKX
Specifies that an exclusive lock is taken on the table held until the end-of-statement or end-of-transaction.
UPDLOCK
Specifies that update locks instead of shared locks are taken while reading the table, and that they are held until the end-of-statement or end-of-transaction.
XLOCK
Specifies that exclusive locks should be taken and held until the end of transaction on all data processed by the statement. If specified with PAGLOCK or TABLOCK, the exclusive locks apply to the appropriate level of granularity.
View Hints
View hints can be used only for indexed views. (An indexed view is a view with a unique clustered index created on it.) If a query contains references to columns that are present both in an indexed view and base tables, and Microsoft SQL Server™ query optimizer determines that using the indexed view provides the best method for executing the query, then the optimizer utilizes the index on the view. This function is supported only on the Enterprise and Developer Editions of the Microsoft SQL Server 2000.
However, in order for the optimizer to consider indexed views, the following SET options must be set to ON:
| ANSI_NULLS | ANSI_WARNINGS | CONCAT_NULL_YIELDS_NULL |
| ANSI_PADDING | ARITHABORT | QUOTED_IDENTIFIERS |
In addition, the NUMERIC_ROUNDABORT option must be set to OFF.
To force the optimizer to use an index for an indexed view, specify the NOEXPAND option. This hint may be used only if the view is also named in the query. SQL Server 2000 does not provide a hint to force a particular indexed view to be used in a query that does not name the view directly in the FROM clause; however, the query optimizer considers the use of indexed views even if they are not referenced directly in the query.
View hints are allowed only in SELECT statements; they cannot be used in views that are the table source in INSERT, UPDATE, and DELETE statements.
Syntax
< view_hint > ::=
{ NOEXPAND [ , INDEX ( index_val [ ,...n ] ) ] }
Arguments
NOEXPAND
Specifies that the indexed view is not expanded when the query optimizer processes the query. The query optimizer treats the view like a table with clustered index.
INDEX ( index_val [ ,...n ] )
Specifies the name or ID of the indexes to be used by SQL Server when it processes the statement. Only one index hint per view can be specified.
INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek.
If multiple indexes are used in the single hint list, the duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the indexed view. The ordering of the indexes in the index hint is significant. A multiple index hint also enforces index ANDing and SQL Server applies as many conditions as possible on each index accessed. If the collection of hinted indexes does not contain all columns referenced in the query, a fetch is performed after retrieving all the indexed columns.
Join Hints
Join hints, which are specified in a query's FROM clause, enforce a join strategy between two tables. If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the position of the ON keywords. In the case of CROSS JOINS, when the ON clauses are not used, parentheses can be used to indicate the join order.
Caution Because the SQL Server query optimizer usually selects the best execution plan for a query, it is recommended that <join_hint>, <query_hint>, and <table_hint> be used only as a last resort by experienced database administrators.
Syntax
< join_hint > ::=
{ LOOP | HASH | MERGE | REMOTE }
Arguments
LOOP | HASH | MERGE
Specifies that the join in the query should use looping, hashing, or merging. Using LOOP | HASH | MERGE JOIN enforces a particular join between two tables.
REMOTE
Specifies that the join operation is performed on the site of the right table. This is useful when the left table is a local table and the right table is a remote table. REMOTE should be used only when the left table has fewer rows than the right table.
If the right table is local, the join is performed locally. If both tables are remote but from different data sources, REMOTE causes the join to be performed on the right table's site. If both tables are remote tables from the same data source, REMOTE is not necessary.
REMOTE cannot be used when one of the values being compared in the join predicate is cast to a different collation using the COLLATE clause.
REMOTE can be used only for INNER JOIN operations.
Remarks
The FROM clause supports the SQL-92-SQL syntax for joined tables and derived tables. SQL-92 syntax provides the INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and CROSS join operators.
Although the outer join operators from earlier versions of SQL Server are supported, you cannot use both outer join operators and SQL-92-style joined tables in the same FROM clause.
UNION and JOIN within a FROM clause are supported within views as well as in derived tables and subqueries.
A self-join is a table that joins upon itself. Inserts or updates that are based on a self-join follow the order in the FROM clause.
Since Microsoft SQL Server™ 2000 considers distribution and cardinality statistics from linked servers that provide column distribution statistics, the REMOTE join hint is not really necessary to force evaluating a join remotely. The SQL Server query processor considers remote statistics and determines if a remote-join strategy is appropriate. REMOTE join hint is useful for providers that do not provide column distribution statistics. For more information, see Distribution Statistics Requirements for OLE DB Providers.
Permissions
FROM permissions default to the permissions for the DELETE, SELECT, or UPDATE statement.
Examples
A. Use a simple FROM clause
This example retrieves the pub_id and pub_name columns from the publishers table.
USE pubs
SELECT pub_id, pub_name
FROM publishers
ORDER BY pub_id
Here is the result set:
pub_id pub_name
------ ---------------------
0736 New Moon Books
0877 Binnet & Hardley
1389 Algodata Infosystems
1622 Five Lakes Publishing
1756 Ramona Publishers
9901 GGG&G
9952 Scootney Books
9999 Lucerne Publishing
(8 row(s) affected)
B. Use the TABLOCK and HOLDLOCK optimizer hints
The following partial transaction shows how to place an explicit shared table lock on authors and how to read the index. The lock is held throughout the entire transaction.
USE pubs
BEGIN TRAN
SELECT COUNT(*)
FROM authors WITH (TABLOCK, HOLDLOCK)
C. Use the SQL-92 CROSS JOIN syntax
This example returns the cross product of the two tables authors and publishers. A list of all possible combinations of au_lname rows and all pub_name rows are returned.
USE pubs
SELECT au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname ASC, pub_name ASC
Here is the result set:
au_lname pub_name
---------------------------------------- -------------------------------
Bennet Algodata Infosystems
Bennet Binnet & Hardley
Bennet Five Lakes Publishing
Bennet GGG&G
Bennet Lucerne Publishing
Bennet New Moon Books
Bennet Ramona Publishers
Bennet Scootney Books
Blotchet-Halls Algodata Infosystems
Blotchet-Halls Binnet & Hardley
Blotchet-Halls Five Lakes Publishing
Blotchet-Halls GGG&G
Blotchet-Halls Lucerne Publishing
Blotchet-Halls New Moon Books
Blotchet-Halls Ramona Publishers
Blotchet-Halls Scootney Books
Carson Algodata Infosystems
Carson Binnet & Hardley
Carson Five Lakes Publishing
...
Stringer Scootney Books
White Algodata Infosystems
White Binnet & Hardley
White Five Lakes Publishing
White GGG&G
White Lucerne Publishing
White New Moon Books
White Ramona Publishers
White Scootney Books
Yokomoto Algodata Infosystems
Yokomoto Binnet & Hardley
Yokomoto Five Lakes Publishing
Yokomoto GGG&G
Yokomoto Lucerne Publishing
Yokomoto New Moon Books
Yokomoto Ramona Publishers
Yokomoto Scootney Books
(184 row(s) affected)
D. Use the SQL-92 FULL OUTER JOIN syntax
This example returns the book title and its corresponding publisher in the titles table. It also returns any publishers who have not published books listed in the titles table, and any book titles with a publisher other than the one listed in the publishers table.
USE pubs
-- The OUTER keyword following the FULL keyword is optional.
SELECT SUBSTRING(titles.title, 1, 10) AS Title,
publishers.pub_name AS Publisher
FROM publishers FULL OUTER JOIN titles
ON titles.pub_id = publishers.pub_id
WHERE titles.pub_id IS NULL
OR publishers.pub_id IS NULL
ORDER BY publishers.pub_name
Here is the result set:
Title Publisher
---------- ----------------------------------------
NULL Five Lakes Publishing
NULL GGG&G
NULL Lucerne Publishing
NULL Ramona Publishers
NULL Scootney Books
(5 row(s) affected)
E. Use the SQL-92 LEFT OUTER JOIN syntax
This example joins two tables on au_id and preserves the unmatched rows from the left table. The authors table is matched with the titleauthor table on the au_id columns in each table. All authors, published and unpublished, appear in the result set.
USE pubs
-- The OUTER keyword following the LEFT keyword is optional.
SELECT SUBSTRING(authors.au_lname, 1, 10) AS Last,
authors.au_fname AS First, titleauthor.title_id
FROM authors LEFT OUTER JOIN titleauthor
ON authors.au_id = titleauthor.au_id
Here is the result set:
Last First title_id
---------- -------------------- --------
White Johnson PS3333
Green Marjorie BU1032
Green Marjorie BU2075
Carson Cheryl PC1035
... ...
McBadden Heather NULL
Ringer Anne PS2091
Ringer Albert PS2091
Ringer Albert PS2106
(29 row(s) affected)
F. Use the SQL-92 INNER JOIN syntax
This example returns all publisher names with the corresponding book titles each publisher has published.
USE pubs
-- By default, SQL Server performs an INNER JOIN if only the JOIN
-- keyword is specified.
SELECT SUBSTRING(titles.title, 1, 30) AS Title, publishers.pub_name
FROM publishers INNER JOIN titles
ON titles.pub_id = publishers.pub_id
ORDER BY publishers.pub_name
Here is the result set:
Title pub_name
------------------------------ ----------------------------------------
The Busy Executive's Database Algodata Infosystems
Cooking with Computers: Surrep Algodata Infosystems
Straight Talk About Computers Algodata Infosystems
But Is It User Friendly? Algodata Infosystems
Secrets of Silicon Valley Algodata Infosystems
Net Etiquette Algodata Infosystems
Silicon Valley Gastronomic Tre Binnet & Hardley
The Gourmet Microwave Binnet & Hardley
The Psychology of Computer Coo Binnet & Hardley
Computer Phobic AND Non-Phobic Binnet & Hardley
Onions, Leeks, and Garlic: Coo Binnet & Hardley
Fifty Years in Buckingham Pala Binnet & Hardley
Sushi, Anyone? Binnet & Hardley
You Can Combat Computer Stress New Moon Books
Is Anger the Enemy? New Moon Books
Life Without Fear New Moon Books
Prolonged Data Deprivation: Fo New Moon Books
Emotional Security: A New Algo New Moon Books
(18 row(s) affected)
G. Use the SQL-92 RIGHT OUTER JOIN syntax
This example joins two tables on pub_id and preserves the unmatched rows from the right table. The publishers table is matched with the titles table on the pub_id column in each table. All publishers appear in the result set, whether or not they have published any books.
USE pubs
SELECT SUBSTRING(titles.title, 1, 30) AS 'Title', publishers.pub_name
FROM titles RIGHT OUTER JOIN publishers
ON titles.pub_id = publishers.pub_id
ORDER BY publishers.pub_name
Here is the result set:
Title pub_name
------------------------------ ----------------------------------------
The Busy Executive's Database Algodata Infosystems
Cooking with Computers: Surrep Algodata Infosystems
Straight Talk About Computers Algodata Infosystems
But Is It User Friendly? Algodata Infosystems
Secrets of Silicon Valley Algodata Infosystems
Net Etiquette Algodata Infosystems
Silicon Valley Gastronomic Tre Binnet & Hardley
The Gourmet Microwave Binnet & Hardley
The Psychology of Computer Coo Binnet & Hardley
Computer Phobic AND Non-Phobic Binnet & Hardley
Onions, Leeks, and Garlic: Coo Binnet & Hardley
Fifty Years in Buckingham Pala Binnet & Hardley
Sushi, Anyone? Binnet & Hardley
NULL Five Lakes Publishing
NULL GGG&G
NULL Lucerne Publishing
You Can Combat Computer Stress New Moon Books
Is Anger the Enemy? New Moon Books
Life Without Fear New Moon Books
Prolonged Data Deprivation: Fo New Moon Books
Emotional Security: A New Algo New Moon Books
NULL Ramona Publishers
NULL Scootney Books
(23 row(s) affected)
H. Use HASH and MERGE join hints
This example performs a three-table join among the authors, titleauthors, and titles tables to produce a list of authors and the books they have written. The query optimizer joins authors and titleauthors (A x TA) using a MERGE join. Next, the results of the authors and titleauthors MERGE join (A x TA) are HASH joined with the titles table to produce (A x TA) x T.
Important After a join hint is specified, the INNER keyword is no longer optional and must be explicitly stated for an INNER JOIN to be performed.
USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ' ' + LTRIM(a.au_lname)), 1, 25)
AS Name, SUBSTRING(t.title, 1, 20) AS Title
FROM authors a INNER MERGE JOIN titleauthor ta
ON a.au_id = ta.au_id INNER HASH JOIN titles t
ON t.title_id = ta.title_id
ORDER BY au_lname ASC, au_fname ASC
Here is the result set:
Warning: The join order has been enforced because a local join hint is used.
Name Title
------------------------- --------------------
Abraham Bennet The Busy Executive's
Reginald Blotchet-Halls Fifty Years in Bucki
Cheryl Carson But Is It User Frien
Michel DeFrance The Gourmet Microwav
Innes del Castillo Silicon Valley Gastr
... ...
Johnson White Prolonged Data Depri
Akiko Yokomoto Sushi, Anyone?
(25 row(s) affected)
I. Use a derived table
This example uses a derived table, a SELECT statement after the FROM clause, to return all authors' first and last names and the book numbers for each title the author has written.
USE pubs
SELECT RTRIM(a.au_fname) + ' ' + LTRIM(a.au_lname) AS Name, d1.title_id
FROM authors a, (SELECT title_id, au_id FROM titleauthor) AS d1
WHERE a.au_id = d1.au_id
ORDER BY a.au_lname, a.au_fname
Here is the result set:
Name title_id
------------------------------------------------------------- --------
Abraham Bennet BU1032
Reginald Blotchet-Halls TC4203
Cheryl Carson PC1035
Michel DeFrance MC3021
Innes del Castillo MC2222
Ann Dull PC8888
Marjorie Green BU1032
Marjorie Green BU2075
Burt Gringlesby TC7777
Sheryl Hunter PC8888
Livia Karsen PS1372
Charlene Locksley PC9999
Charlene Locksley PS7777
Stearns MacFeather BU1111
Stearns MacFeather PS1372
Michael O'Leary BU1111
Michael O'Leary TC7777
Sylvia Panteley TC3218
Albert Ringer PS2091
Albert Ringer PS2106
Anne Ringer MC3021
Anne Ringer PS2091
Dean Straight BU7832
Johnson White PS3333
Akiko Yokomoto TC7777
(25 row(s) affected)