Edit SQL statements to sharpen query results

If your queries aren't working hard enough, adding some basic SQL statements can help focus your results. Let's look at a few types of SQL statements and the clauses or parts that you might edit to get the results you want.

Note:  This article doesn't apply to Access web apps – the kind of database you design with Access and publish online. See Create an Access app for more information.

In this article

Create a Select statement

Customizing the SELECT clause

Customizing the FROM clause

Customizing the WHERE clause

Customizing with the UNION operator

Create a Select statement

A SQL select statement has two to three clauses. The SELECT clause tells the database where to look for the data and asks it to return a specific result.

Note:  SELECT statements always end with a semi-colon (;) either at the end of the last clause or on a line by itself at the end of the SQL statement.

The following select statement asks Access to get information from the E-mail Address and Company columns, from the Contacts table, specifically where it finds "Seattle" in the City column.

The above query has three clauses SELECT, FROM, and WHERE.

1. The SELECT clause lists the columns that contain the data that you want to use and has an operator (SELECT) followed by two identifiers (E-mail Address and Company). If an identifier has spaces or special characters (such as "E-mail Address"), enclose the identifier within square brackets.

2. The FROM clause identifies the source table. In this example, it has an operator (FROM) followed by an identifier (Contacts).

3. The WHERE clause is an optional clause. The example has an operator (WHERE) followed by an expression (City="Seattle").

For more information on select queries see, create a simple select query.

Here is a list of common SQL clauses:

SQL clause	What it does	Required ?
SELECT	Lists the fields that contain data of interest.	Yes
FROM	Lists the tables that contain the fields listed in the SELECT clause.	Yes
WHERE	Specifies field criteria that must be met by each record to be included in the results.	No
ORDER BY	Specifies how to sort the results.	No
GROUP BY	In a SQL statement that contains aggregate functions, lists fields that are not summarized in the SELECT clause.	Only if there are such fields
HAVING	In a SQL statement that contains aggregate functions, specifies conditions that apply to fields that are summarized in the SELECT statement.	No

Each SQL clause is made up of terms. Here is a list of some common SQL terms.

SQL term	Definition	Example
identifier	A name that you use to identify a database object, like the column name.	[E-mail Address] and Company
operator	A keyword that represents an action or modifies an action.	AS
constant	A value that does not change, such as a number or NULL.	42
expression	A combination of identifiers, operators, constants, and functions that evaluates to a single value.	>= Products.[Unit Price]

Top of Page

Customizing the SELECT clause

Customize	Example
To see only the distinct values. Use the DISTINCT keyword in your SELECT clause.	For example, if your customers are from several different branch offices and some have the same telephone number and you want to only see a telephone number listed once, your SELECT clause would be like this: SELECT DISTINCT [txtCustomerPhone]
To change the way an identifier appears in datasheet view to improve readability. Use the AS operator (A keyword that represents an action or modifies an action) with a field alias in your SELECT clause. A field alias is a name that you assign to a field to make the results easier to read.	SELECT [txtCustPhone] AS [Customer Phone]

Customizing the FROM clause

Customize	Example
You can use a table alias or a different name that you assign to a table in a select statement. A table alias is useful if the name of the table name is long, especially when you have multiple fields that have the same name from different tables.	To select data from two fields, both named ID, one of which comes from the table tblCustomer and the other from the table tblOrder: SELECT [tblCustomer].[ID], [tblOrder].[ID] Use the AS operator to define table aliases in the FROM clause: FROM [tblCustomer] AS [C], [tblOrder] AS [O] You can then use these table aliases in your SELECT clause, as follows: SELECT [C].[ID], [O].[ID]
Use joins to combine pairs of records from two data sources into single result or to specify whether to include records from either table if there is no corresponding record in the related table. Join the tables so that the query combines the items from the tables, and excludes items when there is no corresponding record in the other table	Here's what the FROM clause might look like: FROM [tblCustomer] INNER JOIN [tblOrder] ON [tblCustomer].[CustomerID]=[tblOrder].[CustomerID]

About using joins

There are two types of joins, inner and outer joins. Inner joins are more common in queries. When you run a query with an inner join, the result shows only those items where a common value exists in both of the joined tables.

Outer joins specify whether to include data where no common value exists. Outer joins are directional, meaning you can specify whether to include all the records from the first table specified in the join (called a left join), or to include all the records from the second table in the join (called a right join). An outer join has the following SQL syntax:

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

See more information about using joins in a query.

Top of Page

Customizing the WHERE clause

The WHERE clause includes criteria that helps limit the number of items returned in a query. See examples of query criteria and how they work.

An example of how you can customize the basic WHERE clause is to limit the results of a query; Suppose that you want to locate the telephone number of a customer, and can only remember his last name as Bagel. In this example, the last names are stored in a LastName field, so the SQL syntax would be:

WHERE [LastName]='Bagel'

Use the WHERE clause also to combine data sources for columns that have matching data, but different data types. This comes in handy since you cannot create a join between fields that have different data types. Use one field as a criterion for the other field, with the LIKE keyword. For example, if you want to use data from an Assets table and Employees table, only when the type of asset in the asset type field of the Assets table has the number 3 in the Quantity field of the Employees table, here's how your WHERE clause would look:

WHERE field1 LIKE field2

Important:  You cannot specify criteria for a field used with an aggregate function in a WHERE clause. Instead, you use a HAVING clause to specify criteria for aggregated fields.

Top of Page

Customizing with the UNION operator

Use the UNION operator when you want to see a combined view of results from several similar select queries. For example, if your database has a Products table and a Services table and they both have three fields: exclusive offer or product or service, price, warranty or guarantee. Although the Products table stores warranty information, and the Services table stores guarantee information, the basic information is the same. You can use a union query to combine the three fields from the two tables like this:

SELECT name, price, warranty, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee, exclusive_offer 
FROM Services;

When you run the query, data from each set of corresponding fields is combined into one output field. To include any duplicate rows in the results, use the ALL operator.

Note:  The Select statement must have the same number of output fields, in the same order, and with the same or compatible data types. For the purposes of a union query, the Number and Text data types are compatible.

For more information about Union queries, see using a union query to view a unified result from multiple queries.

Top of Page