---
title: WHERE Examples
---
## Simple WHERE Clauses
The `where` method is used to add a WHERE clause to the query, it's an alias for `and` which can be called multiple times
to add multiple **AND** conditions to the WHERE clause, whilst `or` can be used to add an **OR** condition.
$.from(Order)
.leftJoin(Contact, { on:(o,c) => $`${o.contactId} = ${c.id}` })
.where((o,c) => $`${c.id} = ${1} AND ${c.age} > ${18}`)
.or(o => $`${o.total} > ${100}`)
.select('*')
## Array Expansion
Arrays embedded in SQL Fragments are expanded into a list of parameters, this can be used to create IN clauses.
$.from(Contact).where`id IN (${[10,20,30]})`
## WHERE with Subqueries
Fragments can embed other fragments where their SQL and parameters are merged.
const hasPurchasesOver = (c,total) => $`EXISTS (
SELECT 1 FROM Order WHERE o.contactId = ${c.id} AND total >= ${total})`
const inCity = (...cities) => c => $`${c.city} IN (${cities})`
const createdAfter = after => $.sql('createdAt >= $after', { after })
const olderThan = age => ({ sql:'age >= $age', params: { age } })
const q = $.from(Contact,'c')
.where(c => hasPurchasesOver(c,1000))
.and(inCity('Austin','Chicago'))
.and(createdAfter(new Date('2024-01-01')))
.and(olderThan(18))
.and({ contains: { name:'John' } })
db.all(q)
### Subqueries with Query Builders
Similarly, Query Builders and SQL Fragments can be embedded in other Query Builders to create complex subqueries.
## WHERE convenience options
The `where` method can also be called with an object containing a number of convenience options to simplify creating
common queries with an object query. If needed `op` can be used to create options for a custom SQL operator.
const search = {
name: 'John',
age: 27,
city: 'Austin',
}
db.all($.from(Contact).where({ equals: search }))
db.all($.from(Contact).where({ notEquals: search }))
db.all($.from(Contact).where({ like: { name:'John', city:'Austin' } }))
db.all($.from(Contact).where({ notLike: { name:'John', city:'Austin' } }))
db.all($.from(Contact).where({ op: ['>=', { id:10, age:18 }] }))
### LIKE convenience options
The `startsWith`, `endsWith` and `contains` options can be used to create **LIKE** conditions that match the start,
end or any part of a string.
$.from(Contact).where({
startsWith: { city:'A' },
contains: { email:'@gmail.' },
endsWith: { name:'J' },
})
### NULL check convenience options
Whilst the `isNull` and `notNull` convenience options can be used to create **IS NULL** and **IS NOT NULL** conditions.
$.from(Contact).where({
isNull: ['city', 'age'],
notNull: ['email'],
})
## Reset WHERE
Calling `where` with no arguments will reset the WHERE clause:
$.from(Contact).where`name LIKE ${'John%'}`.where().and`id = ${1}`