ALL, DISTINCT, DISTINCTROW Predicates (SQL)

See AlsosqlAllDistinctSee                 ExamplesqlAllDistinctExamples>Low

Description

When you use a SELECT or SELECT...INTO statement, you include all records unless you also use a WHERE clause.

You can also use the ALL, DISTINCT, or DISTINCTROW predicate.  These predicates are optional but when included follow SELECT.  When used, DISTINCT and DISTINCTROW are applied to the query output after all other clauses in the SQL statement.

ALL

If you don't include one of the predicates, ALL is assumed, and all of the records that meet the conditions in the SQL statement29F05E5 are returned.  The following two examples are equivalent and return all records from the Employees table:

SELECT ALL *
FROM Employees
ORDER BY [Employee ID]

SELECT *
FROM Employees
ORDER BY [Employee ID]

DISTINCT

Use DISTINCT when you want to omit records that contain duplicate data in the selected fields.  To appear in the results of the query, the values for each field listed in the SELECT statement must be unique.  For example, several employees listed in an Employees table may have the same last name.  If two records contain Smith in the Last Name field, the following SQL statement returns only one of the records:

SELECT DISTINCT [Last Name]
FROM Employees
WHERE [Last Name] = 'Smith';

If you omit DISTINCT, this query selects both Smith records.

The output of a query that uses DISTINCT is not updatable and doesn't reflect subsequent changes made by other users.

DISTINCTROW

Use DISTINCTROW when you want to omit data based on entire duplicate records, not just duplicate fields.  For example, you might create a query that joins the Customers and Orders tables on the Customer ID field.  The Customers table contains no duplicate Customer ID fields, but the Orders table does because each customer can have many orders.  The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order, but without any details about those orders:

SELECT DISTINCTROW [Company Name]
FROM Customers, Orders,
Customers INNER JOIN Orders
ON Customers.[Customer ID] = Orders.[Customer ID]
ORDER BY [Company Name];

If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order.

DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query.  DISTINCTROW is ignored if your query includes only one table.

Provided you don't select any fields from the "many" table, you can use DISTINCTROW to allow changes to the "one" table in a one-to-many relationship2ARBGXO.  For example, the following SQL statement permits the Comments field to be changed for customers who have large orders:

SELECT DISTINCTROW Customers.[Customer ID], Comments
FROM Customers, Orders,
Customers INNER JOIN Orders
ON Customers.[Customer ID] = Orders.[Customer ID]
WHERE [Order Amount] > 10000

Removing DISTINCTROW or replacing it with ALL or DISTINCT would prevent you from changing the comments, because the Customers table is on the "one" side of a one-to-many relationship.  Including DISTINCTROW makes the "many" table read-only, but the "one" table would be updatable.