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


Related