This tutorial explains how to use null and notNull conditions in where clause using Knex query builder
A basic syntax of knex query builder with null and notNull conditions
db.select()
.from("table")
.where({})
.whereNull("table.col1")
.whereNotNull("table.col2")Let’s have an example of order table which have the following columns of order id, order_placed_date, shipped_date and delivered_date.
| Order_Id | Order_Placed_Date | Shipped_Date | Delivered_Date |
| ABC123 | 2024-01-03 | 2023-01-04 | 2024-01-16 |
| ABC456 | 2024-02-01 | 2024-02-02 | |
| ABC780 | 2024-02-02 | 2024-02-03 |
We would like to retrieve all orders that where shipped but not yet delivered
db.select("order_id")
.from("order")
.whereNull("order.delivered_date")
.whereNotNull("order.shipped_date")The null and notNull conditions can be combined with any where conditions.