And Not

Definition

The andNot methods allows you to add the AND NOT operator to a WHERE clause.

Available methods

1. andNot(KCondition kCondition)

• kCondition: which contains all the information about the condition that will be added to the WHERE clause with an AND NOT operator.

2. andNot(KRaw kRaw)

• kRaw: is a raw content which will be added in the WHERE clause with an AND NOT operator.

Method hierarchy

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

• where, and, andNot, or, orNot

and the subsequent methods that can be called are:

• and, andNot, or, orNot, groupBy, window, except, exceptAll, intersect, intersectAll, union, unionAll, orderBy, limit, offset, fetch, single, multiple

Example: KCondition

Java code:

k
.select(APP_USER.ID, APP_USER.FIRST_NAME)
.from(APP_USER)
.where(APP_USER.CREATED_AT.lt(LocalDateTime.now().minusMonths(1)))
.andNot(APP_USER.EMAIL.ilk("jHonDoE"))
.multiple();

SQL generated:

SELECT au.id, au.first_name
FROM app_user au
WHERE au.created_at < ?1
AND NOT (LOWER(au.email) LIKE ?2)

Parameters:

• ?1: 2022-11-30T13:18:26.390024
• ?2: "jhondoe"

Example: KRaw

Java code:

k
.select(APP_USER.ID, APP_USER.FIRST_NAME)
.from(APP_USER)
.where(raw("au.role_id IS NOT NULL"))
.andNot(raw("au.email IS NOT NULL"))
.multiple();

SQL generated:

SELECT au.id, au.first_name
FROM app_user au
WHERE au.role_id IS NOT NULL
AND NOT (au.email IS NOT NULL)

Parameters:

• None