Operator Reference

This section details usage of the operators that are available to construct SQL expressions.

These methods are presented in terms of the _sql.Operators and _sql.ColumnOperators base classes. The methods are then available on descendants of these classes, including:

_schema.Column objects
_sql.ColumnElement objects more generally, which are the root of all Core SQL Expression language column-level expressions
_orm.InstrumentedAttribute objects, which are ORM level mapped attributes.

The operators are first introduced in the tutorial sections, including:

SQLAlchemy 2.0 Tutorial - unified tutorial in 2.0 style
/orm/tutorial - ORM tutorial in 1.x style
/core/tutorial - Core tutorial in 1.x style

Comparison Operators

Basic comparisons which apply to many datatypes, including numerics, strings, dates, and many others:

_sql.ColumnOperators.__eq__() (Python “==” operator):
```
>>> print(column('x') == 5)
x = :x_1
```
_sql.ColumnOperators.__ne__() (Python “!=” operator):
```
>>> print(column('x') != 5)
x != :x_1
```
_sql.ColumnOperators.__gt__() (Python “>” operator):
```
>>> print(column('x') > 5)
x > :x_1
```
_sql.ColumnOperators.__lt__() (Python “<” operator):
```
>>> print(column('x') < 5)
x < :x_1
```
_sql.ColumnOperators.__ge__() (Python “>=” operator):
```
>>> print(column('x') >= 5)
x >= :x_1
```
_sql.ColumnOperators.__le__() (Python “<=” operator):
```
>>> print(column('x') <= 5)
x <= :x_1
```

_sql.ColumnOperators.between():

>>> print(column('x').between(5, 10))
x BETWEEN :x_1 AND :x_2

IN Comparisons

The SQL IN operator is a subject all its own in SQLAlchemy. As the IN operator is usually used against a list of fixed values, SQLAlchemy’s feature of bound parameter coercion makes use of a special form of SQL compilation that renders an interim SQL string for compilation that’s formed into the final list of bound parameters in a second step. In other words, “it just works”.

IN against a list of values

IN is available most typically by passing a list of values to the _sql.ColumnOperators.in_() method:

>>> print(column('x').in_([1, 2, 3]))
x IN ([POSTCOMPILE_x_1])

The special bound form POSTCOMPILE is rendered into individual parameters at execution time, illustrated below:

>>> stmt = select(User.id).where(User.id.in_([1, 2, 3]))
>>> result = conn.execute(stmt)
{opensql}SELECT user_account.id
FROM user_account
WHERE user_account.id IN (?, ?, ?)
[...] (1, 2, 3){stop}

Empty IN Expressions

SQLAlchemy produces a mathematically valid result for an empty IN expression by rendering a backend-specific subquery that returns no rows. Again in other words, “it just works”:

>>> stmt = select(User.id).where(User.id.in_([]))
>>> result = conn.execute(stmt)
{opensql}SELECT user_account.id
FROM user_account
WHERE user_account.id IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
[...] ()

The “empty set” subquery above generalizes correctly and is also rendered in terms of the IN operator which remains in place.

NOT IN

“NOT IN” is available via the _sql.ColumnOperators.not_in() operator:

>>> print(column('x').not_in([1, 2, 3]))
(x NOT IN ([POSTCOMPILE_x_1]))

This is typically more easily available by negating with the ~ operator:

>>> print(~column('x').in_([1, 2, 3]))
(x NOT IN ([POSTCOMPILE_x_1]))

Tuple IN Expressions

Comparison of tuples to tuples is common with IN, as among other use cases accommodates for the case when matching rows to a set of potential composite primary key values. The _sql.tuple_() construct provides the basic building block for tuple comparisons. The _sql.Tuple.in_() operator then receives a list of tuples:

>>> from sqlalchemy import tuple_
>>> tup = tuple_(column('x', Integer), column('y', Integer))
>>> expr = tup.in_([(1, 2), (3, 4)])
>>> print(expr)
(x, y) IN ([POSTCOMPILE_param_1])

To illustrate the parameters rendered:

>>> tup = tuple_(User.id, Address.id)
>>> stmt = select(User.name).join(Address).where(tup.in_([(1, 1), (2, 2)]))
>>> conn.execute(stmt).all()
{opensql}SELECT user_account.name
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE (user_account.id, address.id) IN (VALUES (?, ?), (?, ?))
[...] (1, 1, 2, 2){stop}
[('spongebob',), ('sandy',)]

Subquery IN

Finally, the _sql.ColumnOperators.in_() and _sql.ColumnOperators.not_in() operators work with subqueries. The form provides that a _sql.Select construct is passed in directly, without any explicit conversion to a named subquery:

>>> print(column('x').in_(select(user_table.c.id)))
x IN (SELECT user_account.id
FROM user_account)

Tuples work as expected:

>>> print(
...     tuple_(column('x'), column('y')).in_(
...         select(user_table.c.id, address_table.c.id).join(address_table)
...     )
... )
(x, y) IN (SELECT user_account.id, address.id
FROM user_account JOIN address ON user_account.id = address.user_id)

Identity Comparisons

These operators involve testing for special SQL values such as NULL, boolean constants such as true or false which some databases support:

_sql.ColumnOperators.is_():

This operator will provide exactly the SQL for “x IS y”, most often seen as “ IS NULL”. The NULL constant is most easily acquired using regular Python None:
```
>>> print(column('x').is_(None))
x IS NULL
```
SQL NULL is also explicitly available, if needed, using the _sql.null() construct:
```
>>> from sqlalchemy import null
>>> print(column('x').is_(null()))
x IS NULL
```
The _sql.ColumnOperators.is_() operator is automatically invoked when using the _sql.ColumnOperators.__eq__() overloaded operator, i.e. ==, in conjunction with the None or _sql.null() value. In this way, there’s typically not a need to use _sql.ColumnOperators.is_() explicitly, paricularly when used with a dynamic value:
```
>>> a = None
>>> print(column('x') == a)
x IS NULL
```
Note that the Python is operator is not overloaded. Even though Python provides hooks to overload operators such as == and !=, it does not provide any way to redefine is.
_sql.ColumnOperators.is_not():

Similar to _sql.ColumnOperators.is_(), produces “IS NOT”:
```
>>> print(column('x').is_not(None))
x IS NOT NULL
```
Is similarly equivalent to != None:
```
>>> print(column('x') != None)
x IS NOT NULL
```

_sql.ColumnOperators.is_distinct_from():

Produces SQL IS DISTINCT FROM:

>>> print(column('x').is_distinct_from('some value'))
x IS DISTINCT FROM :x_1

_sql.ColumnOperators.isnot_distinct_from():

Produces SQL IS NOT DISTINCT FROM:

>>> print(column('x').isnot_distinct_from('some value'))
x IS NOT DISTINCT FROM :x_1

String Comparisons

_sql.ColumnOperators.like():

>>> print(column('x').like('word'))
x LIKE :x_1

_sql.ColumnOperators.ilike():

Case insensitive LIKE makes use of the SQL lower() function on a generic backend. On the PostgreSQL backend it will use ILIKE:
```
>>> print(column('x').ilike('word'))
lower(x) LIKE lower(:x_1)
```

_sql.ColumnOperators.notlike():

>>> print(column('x').notlike('word'))
x NOT LIKE :x_1

_sql.ColumnOperators.notilike():

>>> print(column('x').notilike('word'))
lower(x) NOT LIKE lower(:x_1)

String Containment

String containment operators are basically built as a combination of LIKE and the string concatenation operator, which is || on most backends or sometimes a function like concat():

_sql.ColumnOperators.startswith():

The string containment operators
>>> print(column('x').startswith('word'))
x LIKE :x_1 || '%'

_sql.ColumnOperators.endswith():

>>> print(column('x').endswith('word'))
x LIKE '%' || :x_1

_sql.ColumnOperators.contains():

>>> print(column('x').contains('word'))
x LIKE '%' || :x_1 || '%'

String matching

Matching operators are always backend-specific and may provide different behaviors and results on different databases:

_sql.ColumnOperators.match():

This is a dialect-specific operator that makes use of the MATCH feature of the underlying database, if available:
```
>>> print(column('x').match('word'))
x MATCH :x_1
```

_sql.ColumnOperators.regexp_match():

This operator is dialect specific. We can illustrate it in terms of for example the PostgreSQL dialect:

>>> from sqlalchemy.dialects import postgresql
>>> print(column('x').regexp_match('word').compile(dialect=postgresql.dialect()))
x ~ %(x_1)s

Or MySQL:

>>> from sqlalchemy.dialects import mysql
>>> print(column('x').regexp_match('word').compile(dialect=mysql.dialect()))
x REGEXP %s

String Alteration

_sql.ColumnOperators.concat():

String concatenation:
```
>>> print(column('x').concat("some string"))
x || :x_1
```
This operator is available via _sql.ColumnOperators.__add__(), that is, the Python + operator, when working with a column expression that derives from _types.String:
```
>>> print(column('x', String) + "some string")
x || :x_1
```
The operator will produce the appropriate database-specific construct, such as on MySQL it’s historically been the concat() SQL function:
```
>>> print((column('x', String) + "some string").compile(dialect=mysql.dialect()))
concat(x, %s)
```
_sql.ColumnOperators.regexp_replace():

Complementary to _sql.ColumnOperators.regexp() this produces REGEXP REPLACE equivalent for the backends which support it:
```
>>> print(column('x').regexp_replace('foo', 'bar').compile(dialect=postgresql.dialect()))
REGEXP_REPLACE(x, %(x_1)s, %(x_2)s)
```

_sql.ColumnOperators.collate():

Produces the COLLATE SQL operator which provides for specific collations at expression time:

>>> print((column('x').collate('latin1_german2_ci') == 'Müller').compile(dialect=mysql.dialect()))
(x COLLATE latin1_german2_ci) = %s

To use COLLATE against a literal value, use the _sql.literal() construct:

>>> from sqlalchemy import literal
>>> print((literal('Müller').collate('latin1_german2_ci') == column('x')).compile(dialect=mysql.dialect()))
(%s COLLATE latin1_german2_ci) = x

Arithmetic Operators

_sql.ColumnOperators.__add__(), _sql.ColumnOperators.__radd__() (Python “+” operator):
```
>>> print(column('x') + 5)
x + :x_1

>>> print(5 + column('x'))
:x_1 + x
```
Note that when the datatype of the expression is _types.String or similar, the _sql.ColumnOperators.__add__() operator instead produces string concatenation.
_sql.ColumnOperators.__sub__(), _sql.ColumnOperators.__rsub__() (Python “-” operator):
```
>>> print(column('x') - 5)
x - :x_1

>>> print(5 - column('x'))
:x_1 - x
```
_sql.ColumnOperators.__mul__(), _sql.ColumnOperators.__rmul__() (Python “*” operator):
```
>>> print(column('x') * 5)
x * :x_1

>>> print(5 * column('x'))
:x_1 * x
```
_sql.ColumnOperators.__div__(), _sql.ColumnOperators.__rdiv__() (Python “/” operator):
```
>>> print(column('x') / 5)
x / :x_1
>>> print(5 / column('x'))
:x_1 / x
```
_sql.ColumnOperators.__mod__(), _sql.ColumnOperators.__rmod__() (Python “%” operator):
```
>>> print(column('x') % 5)
x % :x_1
>>> print(5 % column('x'))
:x_1 % x
```

Using Conjunctions and Negations

The most common conjunction, “AND”, is automatically applied if we make repeated use of the _sql.Select.where() method, as well as similar methods such as _sql.Update.where() and _sql.Delete.where():

>>> print(
...        select(address_table.c.email_address).
...        where(user_table.c.name == 'squidward').
...        where(address_table.c.user_id == user_table.c.id)
...    )
SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id

_sql.Select.where(), _sql.Update.where() and _sql.Delete.where() also accept multiple expressions with the same effect:

>>> print(
...        select(address_table.c.email_address).
...        where(
...            user_table.c.name == 'squidward',
...            address_table.c.user_id == user_table.c.id
...        )
...    )
SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id

The “AND” conjunction, as well as its partner “OR”, are both available directly using the _sql.and_() and _sql.or_() functions:

>>> from sqlalchemy import and_, or_
>>> print(
...     select(address_table.c.email_address).
...     where(
...         and_(
...             or_(user_table.c.name == 'squidward', user_table.c.name == 'sandy'),
...             address_table.c.user_id == user_table.c.id
...         )
...     )
... )
SELECT address.email_address
FROM address, user_account
WHERE (user_account.name = :name_1 OR user_account.name = :name_2)
AND address.user_id = user_account.id

A negation is available using the _sql.not_() function. This will typically invert the operator in a boolean expression:

>>> from sqlalchemy import not_
>>> print(not_(column('x') == 5))
x != :x_1

It also may apply a keyword such as NOT when appropriate:

>>> from sqlalchemy import Boolean
>>> print(not_(column('x', Boolean)))
NOT x

Conjunction Operators

The above conjunction functions _sql.and_(), _sql.or_(), _sql.not_() are also available as overloaded Python operators:

Note

The Python &, | and ~ operators take high precedence in the language; as a result, parenthesis must usually be applied for operands that themselves contain expressions, as indicated in the examples below.

_sql.Operators.__and__() (Python “&” operator):

The Python binary & operator is overloaded to behave the same as _sql.and_() (note parenthesis around the two operands):
```
>>> print((column('x') == 5) & (column('y') == 10))
x = :x_1 AND y = :y_1
```
_sql.Operators.__or__() (Python “|” operator):

The Python binary | operator is overloaded to behave the same as _sql.or_() (note parenthesis around the two operands):
```
>>> print((column('x') == 5) | (column('y') == 10))
x = :x_1 OR y = :y_1
```
_sql.Operators.__invert__() (Python “~” operator):

The Python binary ~ operator is overloaded to behave the same as _sql.not_(), either inverting the existing operator, or applying the NOT keyword to the expression as a whole:
```
>>> print(~(column('x') == 5))
x != :x_1

>>> from sqlalchemy import Boolean
>>> print(~column('x', Boolean))
NOT x
```

Operator Customization

TODO

Operator Reference — SQLAlchemy 2.0.0b1 documentation

Contents