Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize
10 out of 12 rated this helpful - Rate this topic

FROM

SQL Server 2000

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 fact table in a star join, SQL Server ignores the index hint and returns a warning message. Also, index ORing is disallowed for a table with an index hint specified.

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)

See Also

CONTAINSTABLE

DELETE

FREETEXTTABLE

INSERT

OPENQUERY

OPENROWSET

Operators

UPDATE

WHERE

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.