DBAtlas · Database operations atlas

UNION / UNION ALL / INTERSECT / EXCEPT (MINUS)

Oracle

-- Union without duplicates
SELECT city FROM customer
UNION
SELECT city FROM supplier;

-- Intersection and difference (MINUS instead of EXCEPT in Oracle)
SELECT city FROM customer INTERSECT SELECT city FROM supplier;
SELECT city FROM customer MINUS SELECT city FROM supplier;

Set operators combine the result sets of two queries. UNION removes duplicates (slower), UNION ALL keeps them all (faster), INTERSECT returns common rows, and the difference operator returns rows found only in the first set. For difference Oracle uses MINUS instead of the standard EXCEPT (EXCEPT also exists since 21c).

PostgreSQL

SELECT city FROM customer
UNION
SELECT city FROM supplier;

SELECT city FROM customer INTERSECT SELECT city FROM supplier;
SELECT city FROM customer EXCEPT SELECT city FROM supplier;

PostgreSQL fully supports standard UNION, UNION ALL, INTERSECT and EXCEPT (EXCEPT, not MINUS, for difference). The ALL form of each operator (INTERSECT ALL, EXCEPT ALL) preserves duplicate counts. Column counts and types must be compatible.

MySQL

SELECT city FROM customer
UNION
SELECT city FROM supplier;

-- INTERSECT/EXCEPT arrive with MySQL 8.0.31+ and MariaDB 10.3+
SELECT city FROM customer INTERSECT SELECT city FROM supplier;

For a long time MySQL supported only UNION / UNION ALL; INTERSECT and EXCEPT were added only in MySQL 8.0.31 (MariaDB 10.3). On older versions use INNER JOIN/EXISTS for intersection and LEFT JOIN ... IS NULL or NOT EXISTS for difference.

SQL Server

SELECT city FROM customer
UNION
SELECT city FROM supplier;

SELECT city FROM customer INTERSECT SELECT city FROM supplier;
SELECT city FROM customer EXCEPT SELECT city FROM supplier;

SQL Server supports standard UNION, UNION ALL, INTERSECT and EXCEPT (2005+); the difference operator is EXCEPT (not MINUS). INTERSECT/EXCEPT compare rows treating NULL values as equal.

MongoDB

// Closest equivalent: $unionWith (MongoDB 4.4+)
db.customer.aggregate([
  { $project: { city: 1, _id: 0 } },
  { $unionWith: { coll: "supplier",
      pipeline: [ { $project: { city: 1, _id: 0 } } ] } },
  { $group: { _id: "$city" } }  // UNION (distinct) effect
])

The MongoDB 4.4+ $unionWith stage combines the results of two collections (like UNION ALL). To remove duplicates, deduplicate afterwards with $group. INTERSECT/EXCEPT have no direct equivalent; they are modeled with $lookup or set operators ($setIntersection, $setDifference).

Redis

# Set operations with the Set data type:
SUNION city_customer city_supplier    # union
SINTER city_customer city_supplier    # intersection
SDIFF  city_customer city_supplier    # difference

Redis has no SQL, but the Set data type offers native set operations: SUNION (union), SINTER (intersection), SDIFF (difference) and their STORE variants that save the result. These operate on pre-built set members, not on query results.

Elasticsearch

# There is no direct set operator.
# For a union, search multiple indices in one query:
GET customer,supplier/_search
{ "query": { "match_all": {} } }

Elasticsearch does not offer UNION/INTERSECT/EXCEPT. A union effect is achieved by querying multiple comma-separated indices; for intersection and difference use the must/should/must_not clauses of a bool query or set logic on the application side.

Open in DBAtlas →