MS-Access / Getting Started

---

FROM Clause

Specifies the tables or queries that provide the source data for your query.

Syntax

FROM {table-name [[AS] correlation-name] |
   select-query-name [[AS] correlation-name] |
   (<select-statement>) AS correlation-name |
   <joined table>},...
  [IN <"source database name"> <[source connect string]>]

where <joined table> is

({table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  <joined table>}
{INNER | {{LEFT | RIGHT | FULL} [OUTER]} JOIN
  {table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  <joined table>}
ON <join-specification>)

where <joined table> is the result of another join operation, and where <join-specification> is a search condition made up of predicates that compare fields in the first table, query, or joined table with fields in the second table, query, or joined table.

Notes You can supply a correlation name for each table name or query name and use this correlation name as an alias for the full table name when qualifying column names in the <fieldlist>, in the <join-specification>, or in the WHERE clause and subclauses. If you're joining a table or a query to itself, you must use correlation names to clarify which copy of the table or query you're referring to in the select list, join criteria, or selection criteria. If a table name or a query name is also an SQL reserved word (for example, Order), you must enclose the name in brackets. In SQL Server, you must enclose the name of a table or query that is also an SQL reserved word in double quotes. If you decide to use quotes, you must also ensure that the server has received the command SET QUOTED_IDENTIFIER ON. Note that when you open a query in an Access project, Access includes the required SET QUOTED_ IDENTIFIER ON command in the command string to ensure that any names that you have enclosed in quotes are recognized correctly by SQL Server. However, if you execute an SQL Server query from a desktop database with a pass-through query, you must either use brackets or quotes and include this command in the pass-through query.

Use INNER JOIN to return all the rows that match the join specification in both tables. Use LEFT [OUTER] JOIN to return all the rows from the first logical table (where logical table is any table, query, or joined table expression) joined on the join specification with any matching rows from the second logical table. When no row matches in the second logical table, the database returns Null values for the columns from that table. Conversely, RIGHT [OUTER] JOIN returns all the rows from the second logical table joined with any matching rows from the first logical table. A FULL [OUTER] JOIN returns all rows from the tables or queries on both sides of the join, but only SQL Server supports this operation.

When you use only equals comparison predicates in the join specification, the result is called an equi-join. The joins that Access displays in the design grid are equi-joins. Access cannot display on the design grid any join specification that uses any comparison operator other than equals (=)-also called a non-equijoin. If you want to define a join on a nonequals comparison (<, >, <>, <=, or >=) in Access, you must define the query using the SQL view. The query designer in an Access project can display non-equijoins. When you join a table to itself using an equals comparison predicate, the result is called a self-join.

SQL Server also supports a CROSS JOIN (with no ON clause). A CROSS JOIN produces the same result as listing table or query names separated by commas with no JOIN specification (a Cartesian product).

If you include multiple tables in the FROM clause with no JOIN specification but do include a predicate that matches fields from the multiple tables in the WHERE clause, the database in most cases optimizes how it solves the query by treating the query as a JOIN. For example:

SELECT *
  FROM TableA, TableB
  WHERE TableA.ID = TableB.ID

is solved by the database as though you had specified

SELECT *
  FROM TableA
    INNER JOIN TableB
    ON TableA.ID = TableB.ID

You cannot update fields in a table by using a recordset opened on the query, the query datasheet, or a form bound to a multiple table query where the join is expressed using a table-list and a WHERE clause. In many cases you can update the fields in the underlying tables when you use the JOIN syntax.

When you list more than one table or query without join criteria, the source is the Cartesian product of all the tables. For example, FROM TableA, TableB instructs the database to fetch all the rows of TableA matched with all the rows of TableB. Unless you specify other restricting criteria, the number of logical rows that the database processes could equal the number of rows in TableA times the number of rows in TableB. When you include the WHERE or HAVING clause, the database returns the rows in which the selection criteria specified in those clauses evaluate to True.

Example

To select information about all companies and contacts and any products purchased, enter the following (qxmplAllCompanyContactsAnyProducts):

SELECT tblCompanies.CompanyName, tblContacts.FirstName,
  tblContacts.LastName, CP.ProductName, CP.DateSold, CP.SoldPrice
 FROM ((tblCompanies
  INNER JOIN tblCompanyContacts
  ON tblCompanies.CompanyID = tblCompanyContacts.CompanyID)
  INNER JOIN tblContacts
  ON tblContacts.ContactID = tblCompanyContacts.ContactID)
  LEFT JOIN
   (SELECT tblContactProducts.ContactID, tblProducts.ProductName,
   tblContactProducts.DateSold, tblContactProducts.SoldPrice
  FROM tblProducts
  INNER JOIN tblContactProducts
  ON tblProducts.ProductID = tblContactProducts.ProductID
  WHERE tblProducts.TrialVersion = 0) AS CP
 ON tblContacts.ContactID = CP.ContactID;

Note If you save the previous query in a previous version of Access, when you open the query in Design view, you'll find that Access saves the inner <select-statement> with brackets:

[SELECT tblContactProducts.ContactID, tblProducts.ProductName,
tblContactProducts.DateSold, tblContactProducts.SoldPrice
FROM tblProducts
INNER JOIN tblContactProducts
ON tblProducts.ProductID = tblContactProducts.ProductID
WHERE tblProducts.TrialVersion = 0]. AS CP

This is the internal syntax supported by the Joint Engine Technology (JET) database engine installed with Access 2003 and earlier. The Access Database Engine (ACE) supplied with Access 2007 and Access 2010 no longer modifies the SQL-you'll find the sample query saved exactly as stated in the example without brackets.