Home

8: Unions And Intersections With Sets

Handling Sets with Union

The UNION operator is used to combine two sets.

(
  SELECT *
FROM products
ORDER BY price DESC
LIMIT 4
)
UNION
(
SELECT *
FROM products
ORDER BY price / weight DESC
LIMIT 4
);

It will also only produce unique results. If that is not what we want, we need to use the UNION ALL keyword to get the duplicates.

A Few Notes on Union

  • It is not strictly required to use the parentheses but it may be confusing for the database and you might get an error.
  • We need the columns and datatypes to be the same.

Commonalities with Intersect

KeywordDescription
UNIONJoin together the results of two queries and remove duplicate rows.
UNION ALLJoin together the results of two queries and keep duplicate rows.
INTERSECTFind the rows common in the two queries. Remove duplicates.
INTERSECT ALLFind the rows common in the two queries. Keep duplicates from individual queries.
EXCEPTFind rows present in first query but not the second. Remove duplicates from LHS. Order matters.
EXCEPT ALLFind rows present in first query but not the second. Keep duplicates from LHS. Order matters.

Repository

https://github.com/okeeffed/developer-notes-nextjs/content/postgresql/SQL-And-PostgreSQL-The-Complete-Developers-Guide/8-Unions-And-Intersections-With-Sets

Sections

Handling Sets with Union
A Few Notes on Union
Commonalities with Intersect

Related

1-Simple-But-Powerful-SQL-Statements
10-Selecting-Distinct-Records
11-Utility-Operators-Keywords-And-Functions
13-PostgreSQL-Complex-Datatypes
14-Database-Side-Validation-And-Constraints
15-Database-Structure-Design-Patterns
16-Building-A-Like-System
17-Building-A-Mention-System
2-Filtering-Records
22-Understanding-The-Internals-Of-PostgreSQL
23-Indexes-For-Performance
24-Basic-Query-Tuning
25-Advanced-Query-Tuning
26-Common-Table-Expressions
27-Recursive-Common-Table-Expressions
28-Simplifying-Queries-With-Views
29-Optimizing-Queries-With-Materialized-Views
3-Working-With-Tables
30-Handling-Concurrency-And-Reversibility-With-Transactions
31-Managing-Database-Design-With-Schema-Migrations
32-Schema-Vs-Data-Migrations
34-Data-Access-Patterns-Repositories
35-Security-Around-PostgreSQL
36-Fast-Parallel-Testing
4-Relating-Records-With-Joins
5-Aggregation-Of-Records
6-Working-With-Large-Datasets
7-Sorting-Records
8-Unions-And-Intersections-With-Sets
9-Assembling-Queries-With-Subqueries
SQL-And-PostgreSQL-The-Complete-Developers-Guide