This chapter uses some specialized terms. Here are some
- base table
- A real table in a FROM list. In queries that involve "virtual"
tables such as views and derived tables, base tables are the underlying tables
to which virtual tables correspond.
- derived table
- A virtual table, such as a subquery given a correlation name or a view.
For example: SELECT derivedtable.c1 FROM (VALUES ('a','b'))
- equality predicate
- A predicate in which one value is
compared to another value using the = operator.
- equijoin predicate
- A predicate in which one column is compared to a column in another table
using the = operator.
- A predicate is optimizable if it provides a
starting or stopping point and allows use of an index. Optimizable predicates
use only simple column references and =, <, >, +, >=,
and IS NULL operators. For complete details, see What's optimizable?. A synonym for optimizable is indexable.
- A WHERE clause contains boolean expressions that can be linked together
by AND or OR clauses. Each part is called a predicate.
For example: WHERE c1 =2 AND c2 = 5 contains two predicates.
- Sargable predicates are a superset of optimizable
predicates; not all sargable predicates are optimizable, because sargable
predicates also include the <> operator. (Sarg stands
for "search argument.") Predicates that are sargable but not optimizable
nevertheless improve performance and allow the optimizer to use more accurate
In addition, sargable predicates can be pushed down (see Predicates pushed into views or derived tables).
- simple column reference
- A reference to a column that is not part of an expression. For example, c1 is a simple column reference, but c1+1, max(c1), and lower(c1) are not.