Except All

Definition

The exceptAll method allows you to add the EXCEPT ALL operator to the query.

Available methods

1. exceptAll(KQueryAllowedToCombining kQueryAllowedToCombining)

• kQueryAllowedToCombining: is a subquery which will be supplied to the EXCEPT ALL operator.

Method hierarchy

The exceptAll method can be used right after the following methods:

• selectDistinct, select1, select, from, innerJoin, leftJoin, rightJoin, fullJoin, crossJoin, where, and, andNot, or, orNot, groupBy, having, and, andNot, or, orNot, window, except, exceptAll, intersect, intersectAll, union, exceptAll

and the subsequent methods that can be called are:

• except, exceptAll, intersect, intersectAll, union, exceptAll, orderBy, limit, offset, fetch, single, multiple

Example

Java code:

final KWhere kQuery1 =
    k
    .select(
        APP_USER.LAST_NAME,
        APP_USER.FIRST_NAME,
        toChar(APP_USER.CREATED_AT, "MM")
    )
    .from(APP_USER)
    .where(toChar(APP_USER.CREATED_AT, "MM").eq("10"))
    .and(cast(toChar(APP_USER.CREATED_AT, "YYYY"), integer()).lt(2022));

k
.select(
    APP_USER.LAST_NAME,
    APP_USER.FIRST_NAME,
    toChar(APP_USER.CREATED_AT, "YYYY")
)
.from(APP_USER)
.where(toChar(APP_USER.CREATED_AT, "YYYY").eq("2022"))
.exceptAll(kQuery1)
.multiple();

SQL generated:

SELECT
    au.last_name,
    au.first_name,
    TO_CHAR(au.created_at, ?1)
FROM app_user au
WHERE TO_CHAR(au.created_at, ?2) = ?3
EXCEPT ALL (
    SELECT
        au.last_name,
        au.first_name,
        TO_CHAR(au.created_at, ?4)
    FROM app_user au
    WHERE TO_CHAR(au.created_at, ?5) = ?6
    AND CAST(TO_CHAR(au.created_at, ?7) AS INTEGER) < ?8
)

Parameters:

• ?1: "YYYY"
• ?2: "YYYY"
• ?3: "2022"
• ?4: "MM"
• ?5: "MM"
• ?6: "10"
• ?7: "YYYY"
• ?8: 2022