Why Do UNION Queries in Microsoft Access Require a Table, Even for Single-Row Selects?

Microsoft Access UNION Queries: The Table Requirement

In Microsoft Access databases, while simple SELECT statements retrieving a single row can omit the FROM clause, UNION or UNION ALL operations necessitate a FROM clause, even for single-row selects.

The Query Limitation

Attempting a UNION or UNION ALL without a data source in the FROM clause results in an error: "Query input must contain at least one table or query." For example:

<code class="language-sql">SELECT "Mike" AS FName
UNION ALL
SELECT "John" AS FName</code>

This query will fail.

Resolving the Issue

To use UNION or UNION ALL with row-based data, a FROM clause is mandatory, regardless of whether the source table's fields are referenced. A solution involves creating a dummy, single-row table:

<code class="language-sql">CREATE TABLE Dual (id COUNTER PRIMARY KEY);
INSERT INTO Dual (id) VALUES (1);
ALTER TABLE Dual ADD CONSTRAINT there_can_be_only_one CHECK ((SELECT Count(*) FROM Dual) = 1);

SELECT "foo" AS my_text
FROM Dual
UNION ALL
SELECT "bar"
FROM Dual;</code>

Another approach involves using TOP 1 or a WHERE clause to limit results to a single row from an existing table.

Important Consideration: The CHECK constraint, introduced in Jet 4, is only compatible with ADO. Using DAO might produce a syntax error when implementing this workaround.

The above is the detailed content of Why Do UNION Queries in Microsoft Access Require a Table, Even for Single-Row Selects?. For more information, please follow other related articles on the PHP Chinese website!