SQL DISTINCTROW

What is SQL DISTINCTROW?

Returns only unique rows in the result set; in MySQL it is a synonym for DISTINCT, while in Microsoft Access it suppresses duplicate rows caused by joined tables.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DISTINCTROW: Supported: MySQL, MariaDB, Microsoft Access Not Supported: PostgreSQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, CockroachDB

SQL DISTINCTROW Full Explanation

DISTINCTROW appears in the SELECT clause to filter out duplicate rows. Its behavior depends on the database engine:• MySQL and MariaDB – DISTINCTROW is parsed as an alias for DISTINCT. The engine compares all selected columns and returns one copy of each unique combination. Using DISTINCTROW instead of DISTINCT has no functional or performance benefit.• Microsoft Access – DISTINCTROW removes duplicate records based on the entire underlying row, not just the columns listed in the SELECT list. It is mostly useful in join queries where multiple child records would otherwise generate duplicates for the parent table’s rows. Access keeps one instance of each parent row even if children differ.DISTINCTROW is not part of ANSI/ISO SQL and is unrecognized by PostgreSQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery, and most other systems. Attempting to use it in those dialects raises a syntax error.Because DISTINCTROW is proprietary and occasionally misunderstood, most practitioners prefer DISTINCT unless they are writing Access-specific queries that need parent-row de-duplication.

SQL DISTINCTROW Syntax

SELECT DISTINCTROW column1, column2, ...
FROM table_name [JOIN ...]
[WHERE condition];

SQL DISTINCTROW Parameters

Example Queries Using SQL DISTINCTROW

-- MySQL example (acts like DISTINCT)
SELECT DISTINCTROW first_name, last_name
FROM employees;

-- Microsoft Access example with join
SELECT DISTINCTROW Orders.CustomerID
FROM Orders INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

Expected Output Using SQL DISTINCTROW

The query returns each unique row once
In MySQL, duplicates across selected columns are removed
In Access, the Orders join will list every CustomerID only once even if multiple orders exist

Use Cases with SQL DISTINCTROW

Eliminate duplicate rows when reading data from MySQL (although DISTINCT is preferred for clarity)
Suppress duplicate parent rows produced by many-to-one joins in Microsoft Access
Quick exploration queries where uniqueness across full rows is required

Common Mistakes with SQL DISTINCTROW

Assuming DISTINCTROW offers additional performance over DISTINCT in MySQL – it does not
Using DISTINCTROW in PostgreSQL, SQL Server, Oracle, or SQLite where it is unsupported
Thinking DISTINCTROW filters only the listed columns in Access – it actually evaluates the entire underlying row

First Introduced In

MySQL 3.23 and Microsoft Access 2.0

Frequently Asked Questions

When should I use DISTINCTROW instead of DISTINCT?

In MySQL never - DISTINCTROW is an alias for DISTINCT. In Microsoft Access use it in join queries when you want each parent row only once, regardless of how many matching child rows exist.

Does PostgreSQL support SELECT DISTINCTROW?

No. PostgreSQL, like most standards-compliant databases, only recognizes DISTINCT. Using DISTINCTROW there leads to a syntax error.

Is DISTINCTROW faster than DISTINCT in MySQL?

No. The MySQL optimizer treats both keywords identically, so there is no performance difference.

Is DISTINCTROW part of ANSI SQL?

No. DISTINCTROW is a proprietary extension found only in MySQL, MariaDB, and Microsoft Access.