DSL Expressions

Literal Expressions

Let's first look at an example (this query has no real business logic, just for demo):

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;

List<
    Tuple5<
        String,
        Long,
        OffsetDateTime,
        String,
        Boolean
    >
> tuples = sqlClient
    .createQuery(book)
    .select(
        Expression.string().value("String"),
        Expression.numeric().value(3L),
        Expression.comparable().value(OffsetDateTime.now()),
        Expression.any().value("String"),
        Expression.nullValue(Boolean.class)
    )
    .execute();

val tuples = sqlClient
    .createQuery(Book::class) {
        select(
            value("String"),
            value(3L),
            value(OffsetDateTime.now()),
            value("String"),
            nullValue(Boolean::class)
        )
    }
    .execute()

The generated SQL is:

select ?, ?, ?, ?, null from BOOK as tb_1_ 

Except for null, literals of other types all become JDBC parameters.

caution

As can be seen from this example, the value() method accepts many parameter types.

Note that no matter what the value() parameter type is, it cannot be null, otherwise exceptions would be thrown.

To create literal expressions for null, the nullValue() method must be used which requires the expression type to be specified.

info

For Java code, type-guided methods are used:

Expression.string(), for string type.
Expression.numeric(), for numeric types.
Expression.comparable(), for comparable types, i.e. types extending java.lang.Comparable.
Expression.any(), for other types.

Kotlin does not have this issue, its API is unified.

tip

In most cases, developers do not need to manually create literal expressions.

Take equality check in examples below for instance, the relatively tedious syntax:

Java
Kotlin

where(
    book.name().eq(
        Expression.string().value("Java in Action")
    )
);

where(
    table.name eq value("Java in Action")
)

Isn't this a bit tedious? Don't worry, it can be replaced with a more convenient form:

Java
Kotlin

where(book.name().eq("Java in Action"));

where(table.name eq "Java in Action")

It's easy to see other APIs provide overloaded versions to avoid making developers create literal expressions manually.

But this can't cover 100% cases. In very rare cases, developers still need to create literal expressions manually.

Constant Expressions

Constant expressions are highly similar to literal expressions, let's look at an example (this query has no real business logic, just for demo):

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;

List<Integer> constants = sqlClient
    .createQuery(book)
    .select(
        Expression.constant(1)
    )
    .execute(); 

val constants = sqlClient
    .createQuery(Book::class) {
        select(
            constant(1)
        )
    }
    .execute()

The generated SQL is:

select
    /* highlight-next-line */
    1
from BOOK as tb_1_

Unlike literal expressions that always use JDBC parameters, constant expressions directly hardcode the value into the SQL.

To prevent SQL injection attacks, constant expressions only support numeric types, this is a hard constraint.

info

Although constant expressions have this hard constraint of only supporting numeric types to prevent SQL injection, they should still be strictly limited in real projects.

The only reason constant expressions exist is that some databases support function-based indexes. Constant expressions can be useful if the SQL expressions defining such function indexes contain numeric constants.

If your project does not have this scenario, never use constant expressions. Always use literal expressions.

Incorrect use of constant expressions can have serious consequences. Erroneously planting unstable numeric variables as constant expressions into SQL will break the stability of the SQL string, eventually leading to extremely low hit rates of execution plan cache in the database, impacting performance.

Static Predicates vs Dynamic Predicates

Initially, Jimmer supported dynamic queries through whereIf:

Java
Kotlin

public List<Book> findBooks(
    @Nullable String name,
    @Nullable Integer edition
) {
    BookTable table = BookTable.$;
    return sqlClient
        .createQuery(table)
        .whereIf(
            name != null && !name.isEmpty(),
            () -> table().name().ilike(name)
        )
        .whereIf(
            edition != null,
            table().edition().eq(edition)
        )
        .select(table)
        .execute();
}

fun findBooks(
    name: String? = null,
    edition: Int? = null
): List<Book> =
    sqlClient
        .createQuery(Book::class) {
            name?.takeIf { it.isNotEmpty() }?.let {
                where(table.name ilike it)
            }
            edition?.let {
                where(table.edition eq it)
            }
            select(table)
        }
        .execute()

Although whereIf has good readability, the code is relatively cumbersome. Later, Jimmer provided dynamic predicates, which simplified the above code to:

Java
Kotlin

public List<Book> findBooks(
    @Nullable String name,
    @Nullable Integer edition
) {
    BookTable table = BookTable.$;
    return sqlClient
        .createQuery(table)
        .where(table.name().ilikeIf(name))
        .where(table.edition().eqIf(edition))
        .select(table)
        .execute();
}

fun findBooks(
    name: String? = null,
    edition: Int? = null
): List<Book> =
    sqlClient
        .createQuery(Book::class) {
            where(table.name `ilike?` name)
            where(table.name `eq?` edition)
            select(table)
        }
        .execute()

Here, ilikeIf, eqIf in Java and ilike?, eq? in Kotlin are called dynamic predicates.

When the right operand is null or an empty string (string of length 0), the dynamic predicate does not create an expression and directly returns null. The where call can automatically ignore null, thereby achieving an equivalent dynamic query.

info

Compared to the initial whereIf, the value of dynamic predicates is not in simplifying the code but in accommodating more complex scenarios, such as or:

Java
Kotlin

public List<Book> findBooksByNameOrPriceRange(
    @Nullable String name,
    @Nullable BigDecimal minPrice,
    @Nullable BigDecimal maxPrice
) {
    BookTable table = BookTable.$;
    return sqlClient
        .createQuery(table)
        .where(
            Predicate.or(
                table.name().eqIf(name),
                table.price().betweenIf(minPrice, maxPrice)
            )
        )
        .select(table)
        .execute();
}

fun findBooksByNameOrPriceRange(
    name: String? = null,
    minPrice: BigDecimal? = null,
    maxPrice: BigDecimal? = null
): List<Book> =
    sqlClient
        .createQuery(Book::class) {
            where(
                or(
                    table.name `eq?` name,
                    table.price.`between?`(minPrice, maxPrice)
                )
            )
            select(table)
        }
        .execute()

Here, all the arguments of or are dynamic predicates, meaning each argument of or can be null, and or can also ignore null arguments. Of course, if all arguments are null, then or itself will refuse to create any expression and directly return null.

Here are all the static predicates and dynamic predicates in Jimmer:

Static Predicate	Dynamic Predicate (Java)	Dynamic Predicate (Kotlin)
eq	eqIf	`eq?`
ne	neIf	`ne?`
lt	ltIf	`lt?`
le	leIf	`le?`
gt	gtIf	`gt?`
ge	geIf	`ge?`
like	likeIf	`like?`
ilike	ilikeIf	`ilike?`
between	betweenIf	`between`

Q: Why does Jimmer provide two sets of predicates instead of just providing dynamic predicates?

A: Because static predicates also handle null specially:

eq: automatically converted to isNull()
ne: automatically converted to isNotNull()
Other static predicates:
- Java: Treats this as a bug in the user's code, requiring the user to either provide a non-null value or switch to a dynamic predicate.
- Kotlin: Prevents the user from providing potentially null values at compile-time.

info

We have clearly discussed the concept of dynamic predicates. To simplify subsequent discussions, the rest of this content will only demonstrate static predicates.

Comparison

Equals

Java
Kotlin

where(book.name().eq("SQL in Action"));

where(table.name eq "SQL in Action")

Not equals

Java
Kotlin

where(book.name().ne("SQL in Action"));

where(table.name ne "SQL in Action")

Greater than

Java
Kotlin

where(book.price().gt(new BigDecimal(50)));

where(table.price gt BigDecimal(50))

Greater than or equals

Java
Kotlin

where(book.price().ge(new BigDecimal(50)));

where(table.price ge BigDecimal(50))

Less than

Java
Kotlin

where(book.price().lt(new BigDecimal(50)));

where(table.price lt BigDecimal(50))

Less than or equals

Java
Kotlin

where(book.price().le(new BigDecimal(50)));

where(table.price le BigDecimal(50))

Between

Java
Kotlin

where(
    book.price().between(
        new BigDecimal(40),
        new BigDecimal(50)
    )
);

where(
    table.price.between(
        BigDecimal(40),
        BigDecimal(50)
    )
)

Not Between

Java
Kotlin

where(
    book.price().notBetween(
        new BigDecimal(40),
        new BigDecimal(50)  
    )
);

where(
    table.price.notBetween(
        BigDecimal(40),
        BigDecimal(50)
    )
)

Fuzzy Matching

Case Sensitivity

like: case sensitive
- Java
- Kotlin
where(book.name().like("Ab"));
where(table.name like "Ab")
The generated SQL condition is:
```
where tb_1_.NAME like ?
```
The corresponding JDBC parameter is: %Ab%
ilike: case insensitive
- Java
- Kotlin
where(book.name().ilike("Ab"));
where(table.name ilike "Ab")
The generated SQL condition is:
- If ilike is supported by dialect
```
where tb_1_.NAME ilike ? 
```
- Otherwise
```
where lower(tb_1_.NAME) like ? 
```
The corresponding JDBC parameter is: %ab%

Match Modes

LikeMode.ANYWHERE (default behavior without specifying): appears anywhere
- Java
- Kotlin
where(book.name().like("Ab", LikeMode.ANYWHERE));
where(table.name.like("Ab", LikeMode.ANYWHERE))
The corresponding JDBC parameter is: %Ab%

LikeMode.START: as prefix

Java
Kotlin

where(book.name().like("Ab", LikeMode.START));

where(table.name.like("Ab", LikeMode.START)) 

The corresponding JDBC parameter is: Ab%

LikeMode.END: as suffix

Java
Kotlin

where(book.name().like("Ab", LikeMode.END));

where(table.name.like("Ab", LikeMode.END))

The corresponding JDBC parameter is: %Ab

LikeMode.EXACT: exact match

Java
Kotlin

where(book.name().like("Ab", LikeMode.EXACT));

where(table.name.like("Ab", LikeMode.EXACT)) 

The corresponding JDBC parameter is: Ab

Null Check

Java
Kotlin

where(book.store().isNull());

where(table.store.isNull())

Java
Kotlin

where(book.store().isNotNull());

where(table.store.isNotNull())

IN LIST

Single Column

Java
Kotlin

where(
    book.name().in(
        Arrays.asList(
            "SQL in Action",  
            "Java in Action"
        )
    )
);

where(
    table.name valueIn listOf(
        "SQL in Action",
        "Java in Action" 
    )
)

The generated SQL condition is:

where tb_1_.NAME in (?, ?)  

Multiple Columns

Java
Kotlin

where(
    Expression.tuple(
        book.name(),
        book.edition()  
    ).in(
        Arrays.asList(
            new Tuple2<>("SQL in Action", 1),
            new Tuple2<>("SQL in Action", 2),
            new Tuple2<>("Java in Action", 1), 
            new Tuple2<>("Java in Action", 2)
        )
    )
);

where(
    tuple(
        table.name,
        table.edition
    ) valueIn listOf(
        Tuple2("SQL in Action", 1),
        Tuple2("SQL in Action", 2),
        Tuple2("Java in Action", 1),
        Tuple2("Java in Action", 2)
    )
)

The generated SQL condition is:

where (tb_1_.NAME, tb_1_.EDITION) in (
    (?, ?), (?, ?), (?, ?), (?, ?) 
)

note

Besides used with collections, in can also be used with subqueries.

This will be covered in detail in docs about subqueries.

Optimization

Jimmer provides 3 optimizations for the in list expression:

Single value optimization
Padding optimization
Any equality optimization

Single value optimization

When the list length is 1, in(?) will be automatically rewritten as = ?, and not in(?) will be automatically rewritten as <> ?. For example:

Java
Kotlin

where(
    Expression.tuple(
        book.name(),
        book.edition()
    ).in(
        Arrays.asList(
            new Tuple2<>("SQL in Action", 1)
        )
    )
);

where(
    tuple(
        table.name,
        table.edition
    ) valueIn listOf(
        Tuple2("SQL in Action", 1)
    )
)

The generated SQL condition:

where
    (tb_1_.NAME, tb_1_.EDITION) =
    (? /* SQL in Action */, ? /* 1 */)

Padding optimization

To support padding optimization, Jimmer provides a switch that can be enabled in two ways:

If you are using the Jimmer Spring Boot Starter, modify application.yml (or application.properties) as follows:
application.yml
```
jimmer:
    in-list-padding-enabled: true
```

If you are not using the Jimmer Spring Boot Starter, you need to call the following API when creating the sqlClient:

Java
Kotlin

JSqlClient sqlClient = JSqlClient
    .newBuilder()
    .setInListPaddingEnabled(true)
    // ...omitted other configurations...
    .build();

val sqlClient = newKSqlClient {
    setInListPaddingEnabled(true)
    // ...omitted other configurations...
}

Once this switch is enabled, Jimmer will ensure that the list length is a power of 2 (2, 4, 6, 8, 16, 32, ...), thereby significantly reducing the diversity of SQL and increasing the diversity of database execution plan caching. For example:

Java
Kotlin

where(
    Expression.tuple(
        book.name(),
        book.edition()
    ).in(
        Arrays.asList(
            new Tuple2<>("SQL in Action", 1),
            new Tuple2<>("SQL in Action", 2),
            new Tuple2<>("Java in Action", 1)
        )
    )
);

where(
    tuple(
        table.name,
        table.edition
    ) valueIn listOf(
        Tuple2("SQL in Action", 1),
        Tuple2("SQL in Action", 2),
        Tuple2("Java in Action", 1)
    )
)

In this example, the list length is 3, which is not a power of 2. The nearest power of 2 to 3 is 4, so Jimmer will repeat the last item in the list, eventually making the list length 4. The generated SQL condition is as follows:

where (tb_1_.NAME, tb_1_.EDITION) in (

    // Original list
    (? /* SQL in Action */, ? /* 1 */),
    (? /* SQL in Action */, ? /* 2 */),
    (? /* Java in Action */, ? /* 1 */),

    // Repeat the last item in the original list 
    // to make the list length a power of 2
    // highlight-next-line
    (? /* Java in Action */, ? /* 1 */)
)

Any equality optimization

This optimization is better tested by the previously discussed padding optimization, but it has two limitations:
- Currently, it can only optimize single column in
- The database needs to support the = any(?) expression, i.e., the isAnyEqualityOfArraySupported() method of the implementation class of org.babyfish.jimmer.sql.dialect.Dialect needs to return true
  Currently, the dialects that support this behavior are:
  - org.babyfish.jimmer.sql.dialect.H2Dialect
  - org.babyfish.jimmer.sql.dialect.PostgresDialect
For this, Jimmer provides a switch that can be enabled in two ways:
- If you are using the Jimmer Spring Boot Starter, modify application.yml (or application.properties) as follows:
  application.yml
```
jimmer:
    in-list-to-any-equality-enabled: true
```
- If you are not using the Jimmer Spring Boot Starter, you need to call the following API when creating the sqlClient:
  - Java
  - Kotlin
  JSqlClient sqlClient = JSqlClient .newBuilder() .setInListToAnyEqualityEnabled(true) // ...omitted other configurations... .build();
  val sqlClient = newKSqlClient { setInListToAnyEqualityEnabled(true) // ...omitted other configurations... }
warning
If the current org.babyfish.jimmer.sql.dialect.Dialect implementation class's isAnyEqualityOfArraySupported() method returns false (does not support this feature), this configuration will cause an exception, and the sqlClient cannot be created, and the application cannot start.
- Java
- Kotlin
where( book.name().in( Arrays.asList( "SQL in Action", "Java in Action" ) ) );
where( table.name valueIn listOf( "SQL in Action", "Java in Action" ) )
The generated SQL condition:
```
where tb_1_.NAME = any(
    ? /* [SQL in Action, Java in Action] */
)
```
Here, the entire list is treated as a whole and passed as an array-type parameter.

AND, OR, NOT

AND

Java
Kotlin

where(
    Predicate.and(
        book.name().like("Ab"),
        book.price().ge(new BigDecimal(40)), 
        book.price().lt(new BigDecimal(60))
    )
);

where(
    and(
        table.name like "Ab",
        table.price ge BigDecimal(40),
        table.price lt BigDecimal(60)
    )
) 

note

Note that when the logical AND expression is directly used as the where parameter, the following equivalent forms are more recommended:

Use varargs version of where method

Java
Kotlin

where(
    book.name().like("Ab"),
    book.price().ge(new BigDecimal(40)),
    book.price().lt(new BigDecimal(60))
);

where(
    table.name like "Ab",
    table.price ge BigDecimal(40), 
    table.price lt BigDecimal(60)
)

Call where multiple times

Java
Kotlin

q.where(book.name().like("Ab"));
q.where(book.price().ge(new BigDecimal(40)));
q.where(book.price().lt(new BigDecimal(60)));

where(table.name like "Ab")
where(table.price ge BigDecimal(40)) 
where(table.price lt BigDecimal(60))

So directly using and would not be common in real projects.

OR

Java
Kotlin

where(
    Predicate.or(
        book.name().like("Ab"),
        book.price().ge(new BigDecimal(40)),
        book.price().le(new BigDecimal(60))
    )
);

where(
    or(
        table.name like "Ab",
        table.price ge BigDecimal(40),
        table.price le BigDecimal(60)
    )
)

NOT

Java
Kotlin

where(
    book.name().like("Ab").not()  
);

where(
    not(table.name like "Ab")
);

It's not always necessary to call the not() function. Often there are shortcuts available, such as:

.eq(value).not() can be simplified to .ne(value)
.isNull().not() can be simplified to .isNotNull(value)
.exists().not() can be simplified to .notExists() (exists will be introduced in subqueries)

Even when developers explicitly use not(), the final SQL may not contain NOT. For example:

Java
Kotlin

where(
    book.price().ge(new BigDecimal(40)).not()
);

where(
    not(book.price ge BigDecimal(40)) 
)

The actual generated SQL is:

where tb_1_1.PRICE < ?

Jimmer-sql tries to avoid directly using NOT in SQL, but the final logic is equivalent to what you want.

Math Operations

Java
Kotlin

select(book.price().plus(BigDecimal.TWO));

select(table.price + BigDecimal.TWO)

Java
Kotlin

select(book.price().minus(BigDecimal.TWO));

select(table.price - BigDecimal.TWO)

Java
Kotlin

select(book.price().times(BigDecimal.TWO)); 

select(table.price * BigDecimal.TWO)

Java
Kotlin

select(book.price().div(BigDecimal.TWO));

select(table.price / BigDecimal.TWO)

Java
Kotlin

select(book.price().rem(BigDecimal.TWO));

select(table.price % BigDecimal.TWO)

Aggregate Functions

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;

List<
    Tuple6<
        Long,
        Long,
        BigDecimal,
        BigDecimal,
        BigDecimal,
        BigDecimal  
    >
> tuples = sqlClient
    .createQuery(book)
    .select(
        book.count(),
        book.id().count(/* disintct */ true),
        book.price().sum(),
        book.price().min(),
        book.price().max(),
        book.price().avg()
    )
    .execute();

val tuples = sqlClient
    .createQuery(Book::class) {
        select(
            count(table),
            count(table.id, distinct = true),
            sum(table.price),
            min(table.price),
            max(table.price),
            avg(table.price)
        );
    }
    .execute()

The final generated SQL is:

select
    count(tb_1_.ID),
    count(distinct tb_1_.ID),
    sum(tb_1_.PRICE),
    min(tb_1_.PRICE),
    max(tb_1_.PRICE),
    avg(tb_1_.PRICE)
from BOOK as tb_1_ 

Coalesce

The Coalesce statement specifies a list of expressions and returns the first non-null value.

Java
Kotlin

BookStoreTable store = Tables.BOOK_STORE_TABLE;

List<String> results = sqlClient
    .createQuery(store) 
    .select(
        store.website() ❶
            .coalesceBuilder()
            .or(store.name()) ❷
            .or("Default Value") ❸
            .build()
    )
    .execute();

var results = sqlClient
    .createQuery(BookStore::class) {
        select(
            table.website
                .coalesce() ❶
                .or(table.name) ❷
                .end()
        )
    }

tip

In this example, the Kotlin code is different from the Java code. In Kotlin there is no expression ❸.

The Kotlin API introduces null-safety for SQL, table.name is a non-null field, so Kotlin forces us to write end() to complete the expression.

This means the expression ❸ in Java code will not be used.

The generated SQL is:

SQL for Java
SQL for Kotlin

select
    /* highlight-next-line */
    coalesce(
        tb_1_.WEBSITE,
        tb_1_.NAME,
        ?  
    )
from BOOK_STORE as tb_1_ 

select
    /* highlight-next-line */
    coalesce(
        tb_1_.WEBSITE,
        tb_1_.NAME 
    )
from BOOK_STORE as tb_1_

Specifically, if the SQL coalesce function has only two parameters, i.e. or() is called only once in the Java code above, there is a shortcut:

Java
Kotlin

BookStoreTable store = Tables.BOOK_STORE_TABLE;

List<String> results = sqlClient
    .createQuery(store)
    .select(
        store.website().coalesce(store.name()) 
    )
    .execute();

var results = sqlClient
    .createQuery(BookStore::class) {
        select(
            table.website.coalesce(table.name)
        )
    }

The generated SQL is:

select
    /* highlight-next-line */
    coalesce(tb_1_.WEBSITE, tb_1_.NAME)
from BOOK_STORE as tb_1_ 

This form is actually the most common.

Concat

The Contact expression concatenates strings.

This example concatenates firstName and lastName of authors with a space as separator:

Java
Kotlin

AuthorTable author = Tables.AUTHOR_TABLE;  

List<String> results = sqlClient
    .createQuery(author)
    .select(
        author
            .firstName()
            .concat(
                Expression.string().value(" "), 
                author.lastName()
            )
    )
    .execute();

var fullNames = sqlClient
    .createQuery(Author::class) {
        select(
            concat(
                table.firstName,
                value(" "),
                table.lastName
            )
        )
    }
    .execute()

The generated SQL is:

select
    /* highlight-next-line */
    concat(
        tb_1_.FIRST_NAME,
        ?,
        tb_1_.LAST_NAME  
    )
from AUTHOR as tb_1_

Case

There are two types of case expressions, simple case and searched case.

Simple Case

For simple case, an expression is specified at the beginning. Each WHEN branch specifies an expected value to check if the expression matches that value.

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;

List<String> results = sqlClient
    .createQuery(book)
    .select(
        Expression.string()
            .caseBuilder(book.name())
            .when("Java in Action", "Matched")
            .when("SQL in Action", "Matched")
            .otherwise("Not matched")
    )
    .execute();

val results = sqlClient
    .createQuery(Book::class) {
        select(
            case(table.name)
                .match("Kotlin in Action", "Matched")
                .match("SQL in Action", "Matched")
                .otherwise("Not matched")
        )
    }
    .execute()

The generated SQL is:

select
    /* highlight-next-line */
    case tb_1_.NAME
        when ? then ?
        when ? then ?
        else ?
    end
from BOOK as tb_1_ 

Searched Case

For searched case, no expression is specified at the beginning. Each WHEN branch can specify an arbitrary complex conditional expression to check if any branch logic evaluates to true.

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;

List<String> results = sqlClient
    .createQuery(book)
    .select(
        Expression.string()
            .caseBuilder()
            .when(
                book.price().lt(new BigDecimal(30)), 
                "Cheap"
            )
            .when(
                book.price().gt(new BigDecimal(70)),
                "Expensive"  
            )
            .otherwise("Appropriate")
    )
    .execute();

val results = sqlClient
    .createQuery(Book::class) {
        select(
            case()
                .match(table.price lt BigDecimal(30), "Cheap")
                .match(table.price gt BigDecimal(70), "Expensive")
                .otherwise("Appropriate")
        )
    }
    .execute() 

The generated SQL is:

select
    /* highlight-next-line */
    case
        when tb_1_.PRICE < ? then ?
        when tb_1_.PRICE > ? then ?
        else ?
    end
from BOOK as tb_1_

Literal Expressions​

Constant Expressions​

Static Predicates vs Dynamic Predicates​

Comparison​

Fuzzy Matching​

Case Sensitivity​

Match Modes​

Null Check​

IN LIST​

Single Column​

Multiple Columns​

Optimization​

AND, OR, NOT​

AND​

OR​

NOT​

Math Operations​

Aggregate Functions​

Coalesce​

Concat​

Case​

Simple Case​

Searched Case​

Literal Expressions

Constant Expressions

Static Predicates vs Dynamic Predicates

Comparison

Fuzzy Matching

Case Sensitivity

Match Modes

Null Check

IN LIST

Single Column

Multiple Columns

Optimization

AND, OR, NOT

AND

OR

NOT

Math Operations

Aggregate Functions

Coalesce

Concat

Case

Simple Case

Searched Case