Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements.
[ FROM {<table_source>} [,...n] ]
<table_source> ::=
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>
<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
FROM tab1 LEFT OUTER JOIN tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1
ON tab2.c3 = tab4.c3
For multiple CROSS joins, use parentheses to change the natural order of the joins.
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.
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.
For more information about search conditions and predicates, see Search Condition.
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>, and <table_hint> only be used as a last resort by experienced database administrators.
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.
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:
The NOLOCK, READUNCOMMITTED, and READPAST table hints are not allowed for tables that are targets of delete, insert, or update operations.
<table_hint> ::=
{ INDEX(index_val [,...n])
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}
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.
Any index hints specified for a view are ignored, and SQL Server returns a warning message.
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.
Note If you receive error messages 605, 606, 624, or 625 when READUNCOMMITTED is specified, resolve them as you would a deadlock error (1205), and retry your statement.
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 FULL OUTER 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.
<join_hint> ::=
{ LOOP | HASH | MERGE | REMOTE }
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.
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, and FULL OUTER 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.
FROM permissions default to the permissions for the DELETE, SELECT, or UPDATE statement.
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)
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)
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)
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)
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)
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)
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)
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)
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)
| CONTAINSTABLE | OPENROWSET |
| DELETE | Operators |
| FREETEXTTABLE | UPDATE |
| INSERT | WHERE |
| OPENQUERY |