Access SQL: FROM clause

This is one of a set of articles about Access SQL. This article describes how to write a FROM clause, and uses examples to illustrate various techniques that you can use in a FROM clause.

In a SELECT statement, you specify data sources in the FROM clause. The FROM clause may also contain a JOIN operation. You use a JOIN operation to match and combine data from two data sources, such as two tables, or a table and a query.

For an overview of Access SQL, see the article Access SQL: basic concepts, vocabulary, and syntax. For more information about other parts of Access SQL, see the See Also section.

In this article


Specify data sources

In a SELECT statement, the FROM clause specifies the tables or queries that contain the data that the SELECT clause will use.

Suppose that you want to know the telephone number of a specific customer. Assuming that the table that contains the field that stores this data is called tblCustomer, the FROM clause would resemble the following:

FROM tblCustomer

You can use square brackets to enclose the name. If the name does not contain any spaces or special characters (such as punctuation marks), the square brackets are optional. If the name does contain spaces or special characters, you must use the brackets.

 Tip   A name that contains spaces is easier to read and can save you time when you design forms and reports, but may end up making you type more when you write SQL statements. You should consider this fact when you name objects in your database.

Top of Page Top of Page

Use substitute names for data sources

You can use a different name to refer to a table in a SELECT statement by using a table alias in your FROM clause. A table alias is a name that you assign to a data source in a query when you use an expression as a data source, or to make the SQL statement easier to type and read. This can be especially useful if the name of the data source is long or difficult to type, especially when there are multiple fields that have the same name from different tables.

For example, if you want to select data from two fields, both named ID, one of which comes from the table tblCustomer and the other from the table tblOrder, your SELECT clause might resemble the following:

SELECT [tblCustomer].[ID], [tblOrder].[ID]

By using table aliases in your FROM clause, you could make the query easier to type. Your FROM clause, with table aliases, might resemble the following:

FROM [tblCustomer] AS [C], [tblOrder] AS [O]

You could then use those table aliases in your SELECT clause, as follows:

SELECT [C].[ID], [O].[ID]

 Note   When you use a table alias, you can refer to the table in your SQL statement by using the alias or by using the full table name.

Top of Page Top of Page

Join related data

When you need a way to combine pairs of records from two data sources into single records in a query result, you can perform a join. A join is a SQL operation that specifies how two data sources are related, and whether data from one source should be included if there is no corresponding data from the other source.

To combine the information from two data sources, you perform a join operation on the field that they have in common. When the values stored in this field match, the data from the records is combined in the results.

In addition to combining data, you also use a join to specify whether to include records from either table if there is no corresponding record in the related table.

For example, suppose you want to use data from two tables in a query: tblCustomer and tblOrder. The two tables both have a field, CustomerID, that identifies a customer. Each record in the tblCustomer table may have one or more corresponding records in the tblOrder table, and the corresponding values can be determined by values in the CustomerID field.

If you want to join the tables so that the query combines the records from the tables, excluding records from either table if there is no corresponding record in ther other table, your FROM clause might resemble the following (line break added here for readability):

FROM [tblCustomer] INNER JOIN [tblOrder]
 ON [tblCustomer].[CustomerID]=[tblOrder].[CustomerID]

In Microsoft Office Access, joins occur in the FROM clause of a SELECT statement. There are two types of joins: inner joins and outer joins. The following sections explain these two types of joins.

Inner joins

Inner joins are the most common type of join. When a query with an inner join is run, the only records that are included in the query results are those records where a common value exists in both of the joined tables.

An inner join has the following syntax (line break added here for readability):

FROM  INNER JOIN 
 ON .  .

The following table describes the different parts of an INNER JOIN operation.

Part Description
table1, table2 The names of the tables from which records are combined.
field1, field2 The names of the fields that are joined. If they are not numeric, the fields must be of the same data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) and contain the same kind of data, but they do not have to have the same name.
compopr Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>."

Outer joins

Outer joins are similar to inner joins because they tell a query how to combine information from two sources. They are different because they also specify whether to include data where no common value exists. Outer joins are directional: you can specify whether to include all the records from the first data source specified in the join (called a left join), or to include all the records from the second data source in the join (called a right join).

An outer join has the following syntax:

FROM table1 [ LEFT | RIGHT ] JOIN table2
ON table1.field1compopr table2.field2

The following table describes the different parts of LEFT JOIN and RIGHT JOIN operations.

Part Description
table1, table2 The names of the tables from which records are combined.
field1, field2 The names of the fields that are joined. The fields must be of the same data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) and contain the same kind of data, but they do not have to have the same name.
compopr Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>."

For more information about joins, see the article Join tables and queries.

Top of Page Top of Page

 
 
Applies to:
Access 2007