UNION, INTERSECT, EXCEPT | Pricefx Knowledge Base

UNION combines the results of two or more SELECT statements while removing duplicate rows, while UNION ALL combines them and includes all duplicate rows.
INTERSECT is an operator that retrieves the common rows between two query results. INTERSECT ALL returns also duplicate rows.
The EXCEPT clause in SQL is used to return distinct rows from the first query that are not present in the results of the second query. It essentially performs a set difference operation, allowing you to filter out records that exist in one dataset but not another. EXCEPT ALL keeps duplicated rows.

info UNION ALL, INTERSECT ALL and EXCEPT ALL are faster because they skip the duplicate-checking step. Choose UNION, INTERSECT, EXCEPT when you need a unique set of records, and UNION ALL, INTERSECT ALL, EXCEPT ALL when you want all records regardless of duplicates for better performance.

UNION

Code

Groovy

def qapi = api.queryApi()

def mainProductDs = qapi.tables().dataSource("product")
def otherProductDs = qapi.tables().dataSource("otherProduct")

def pOtherProduct = qapi.source(otherProductDs, [otherProductDs.sku, otherProductDs.price, otherProductDs.uom])

qapi.source(mainProductDs, [mainProductDs.sku, mainProductDs.price, mainProductDs.uom])
        .union(pOtherProduct)
        .stream { it.toList() }

Result

Data from otherProduct Data Source:

sku	price	notes
Product 1	10	note 1
Product 2	12	note 2
Product 3	18	note 3

Data from product Data Source:

sku	price	notes
Product 2	12	note 2
Product 3	18	note 3
Product 4	20	note 4

Result with the UNION operator applied – duplicates have been removed:

sku	price	notes
Product 1	10	note 1
Product 2	12	note 2
Product 3	18	note 3
Product 4	20	note 4

UNION ALL

Code

Groovy

def qapi = api.queryApi()

def mainProductDs = qapi.tables().dataSource("product")
def otherProductDs = qapi.tables().dataSource("otherProduct")

def pOtherProduct = qapi
        .source(otherProductDs, [otherProductDs.sku, otherProductDs.price])

qapi.source(mainProductDs, [mainProductDs.sku, mainProductDs.price])
        .unionAll(pOtherProduct)  // keep duplicates; use .union(...) if you want distinct rows
        .sortBy { cols -> [
                qapi.orders().ascNullsFirst(cols.sku),
                qapi.orders().ascNullsFirst(cols.price)
        ] }
        .stream { it.toList() }

Result

Data from otherProduct Data Source:

sku	price
Product 1	10
Product 2	12
Product 3	18

Data from product Data Source:

sku	price
Product 2	12
Product 3	18
Product 4	20

Result with the UNION ALL operator applied – duplicates are kept:

sku	price
Product 1	10
Product 2	12
Product 3	18
Product 2	12
Product 3	18
Product 4	20

Note: The order of the rows is not guaranteed. If you want to make sure the rows are returned in a certain order, use .sortBy in the queries.

INTERSECT

Code

Groovy

def qapi = api.queryApi()

def mainProductDs = qapi.tables().dataSource("product")
def otherProductDs = qapi.tables().dataSource("otherProduct")

def pOtherProduct = qapi
        .source(otherProductDs, [otherProductDs.sku])

qapi.source(mainProductDs, [mainProductDs.sku])
        .intersect(pOtherProduct)
        .stream { it.toList() }

Result

Data from otherProduct Data Source:

sku
Product 1
Product 2
Product 3

Data from product Data Source:

sku
Product 2
Product 3
Product 4

Result with the INTERSECT operator applied – duplicates have been removed:

sku
Product 2
Product 3

INTERSECT ALL

Code

Groovy

def qapi = api.queryApi()

def mainProductDs = qapi.tables().dataSource("product")
def otherProductDs = qapi.tables().dataSource("otherProduct")

def pOtherProduct = qapi
        .source(otherProductDs, [otherProductDs.sku])

qapi.source(mainProductDs, [mainProductDs.sku])
        .intersectAll(pOtherProduct)   // multiset intersection (keeps duplicates)
        .stream { it.toList() }

Result

Data from otherProduct Data Source:

sku
Product 1
Product 2
Product 3

Data from product Data Source:

sku
Product 2
Product 3
Product 4

Result with the INTERSECT ALL operator applied – duplicates are kept:

sku
Product 2
Product 3
Product 2
Product 3

EXCEPT

Code

Groovy

def qapi = api.queryApi()

def mainProductDs = qapi.tables().dataSource("product")
def otherProductDs = qapi.tables().dataSource("otherProduct")

def pOtherProduct = qapi
        .source(otherProductDs, [otherProductDs.sku])

qapi.source(mainProductDs, [mainProductDs.sku])
        .except(pOtherProduct)
        .stream { it.toList() }

Result

Data from otherProduct Data Source:

sku
Product 1
Product 1
Product 2
Product 3

Data from product Data Source:

sku
Product 2
Product 3
Product 4

Result with the EXCEPT operator applied – duplicated “Product 1” has been removed:

sku
Product 1

EXCEPT ALL

Code

Groovy

def qapi = api.queryApi()

def mainProductDs = qapi.tables().dataSource("product")
def otherProductDs = qapi.tables().dataSource("otherProduct")

def pOtherProduct = qapi
        .source(otherProductDs, [otherProductDs.sku])

qapi.source(mainProductDs, [mainProductDs.sku])
        .exceptAll(pOtherProduct)
        .stream { it.toList() }

Result

Data from otherProduct Data Source:

sku
Product 1
Product 1
Product 2
Product 3

Data from product Data Source:

sku
Product 2
Product 3
Product 4

Result with the EXCEPT ALL operator applied – duplicated “Product 1” is kept:

sku
Product 1
Product 1