SQL Operators
Introduction
SQL operators are symbols or keywords that tell a database to perform an operation on one or more values. They calculate totals, compare columns, combine conditions, test ranges, search patterns, check missing values, determine whether related rows exist, and combine query results.
salary + bonus
total_amount > 100
status = 'ACTIVE' AND department_id IN (10, 20)
An operator usually works inside an expression. The operands appear around or after the operator, and the database evaluates the complete expression to produce a value or logical result.
What Is an SQL Operator?
An SQL operator defines a particular operation. In salary > 50000, the values of salary and 50000 are operands, while the greater-than symbol is the operator. The result is true, false, or unknown because SQL includes missing information represented by NULL.
Operators are not complete database instructions by themselves. They become useful within expressions, clauses, and statements.
Operators, Expressions, Clauses, and Functions
| Concept | Example | Purpose |
|---|---|---|
| Operator | + | Defines an operation |
| Expression | salary + bonus | Produces a value |
| Clause | WHERE salary > 50000 | Performs a role in a statement |
| Function | ROUND(total_amount, 2) | Returns a value from arguments |
| Statement | SELECT ... FROM ... WHERE ... | Provides a complete instruction |
Some words, such as AND, are both SQL keywords and operators in context. Classification matters less than understanding what result the construct produces and where it can legally appear.
Main SQL Operator Categories
The main categories are arithmetic, comparison, logical, range, membership, pattern matching, NULL testing, existence and quantified comparison, string concatenation, set, and bitwise operators. Exact syntax and support vary among MySQL, PostgreSQL, SQL Server, Oracle, SQLite, and other database systems.
| Category | Examples | Typical result |
|---|---|---|
| Arithmetic | + - * / | Numeric value |
| Comparison | = <> > < >= <= | Logical result |
| Logical | AND OR NOT | Combined logical result |
| Range and membership | BETWEEN IN | Logical result |
| Pattern | LIKE | Logical result |
| NULL | IS NULL | Logical result |
| Existence | EXISTS | Logical result |
| Set | UNION INTERSECT EXCEPT | Combined result set |
Arithmetic Operators
Arithmetic operators perform mathematical calculations. Addition and subtraction combine or adjust values, multiplication derives products, division creates ratios, and modulo returns a remainder where supported.
SELECT
salary + bonus_amount AS gross_pay,
list_price - discount_amount AS sale_price,
quantity * unit_price AS line_total,
total_amount / installment_count AS installment_amount
FROM transactions;
Result types depend on operand types and database rules. Exact numeric types are generally appropriate for money, while floating-point values can introduce binary approximation.
Addition and Subtraction
SELECT employee_id,
salary + COALESCE(bonus_amount, 0) AS total_compensation
FROM employees;
SELECT product_id,
price - discount_amount AS final_price
FROM products;
Addition and subtraction appear simple, but nullable operands and mixed data types change the result. Use COALESCE only when the business rule explicitly treats a missing amount as zero.
Multiplication and Division
SELECT order_id,
quantity * unit_price AS gross_amount,
quantity * unit_price / package_size AS amount_per_unit
FROM order_items;
Division requires special attention. A zero denominator can raise an error, and integer operands can produce integer division in some systems. Explicit decimal conversion makes the desired scale clearer.
CAST(quantity AS DECIMAL(12, 2))
/ NULLIF(package_size, 0)
NULLIF changes a zero denominator to NULL, producing an undefined result rather than an exception. Use this only if NULL correctly represents the business outcome.
Modulo and Unary Operators
Modulo returns the remainder after division and is useful for cyclic distribution and even-or-odd tests.
WHERE order_id % 2 = 0
Some databases use the percent symbol while others also or instead provide a MOD() function. Unary minus changes a numeric sign, as in -balance; unary plus normally preserves it. Verify target-database syntax before relying on these forms.
Arithmetic Precedence
Multiplication, division, and modulo normally bind before addition and subtraction. Parentheses override the normal order and document intent.
10 + 2 * 3 -- Evaluates as 10 + 6
(10 + 2) * 3 -- Evaluates as 12 * 3
Do not depend on readers remembering every precedence level. Parentheses are inexpensive and prevent business calculations from being misread.
Arithmetic with NULL
An arithmetic operation involving NULL normally returns NULL because the missing value makes the result unknown.
salary + bonus_amount
If bonus_amount is NULL, the total is generally NULL. Replacing it with zero is appropriate only when missing means no bonus, not when the amount has not yet been determined.
Comparison Operators
Comparison operators test relationships between expressions.
status = 'ACTIVE'
department_id <> 20
salary > 50000
price < 100
score >= 80
stock_quantity <= reorder_level
The standard not-equal operator is <>. Many databases also support !=, but == is not standard SQL equality and should not be used unless a specific dialect explicitly documents it.
Comparison Results and Three-Valued Logic
A SQL comparison can be true, false, or unknown. Unknown appears when the database cannot determine the relationship, commonly because an operand is NULL.
discount_amount > 0
If discount_amount is missing, SQL cannot conclude that the condition is true or false. A WHERE clause retains only true rows, so both false and unknown rows are filtered out.
Comparison Compatibility and Collation
Comparison is meaningful only when the operands have compatible types. Comparing an integer column with a character parameter may force implicit conversion, fail when text is invalid, or prevent efficient index use. Application code should bind parameters with the column's intended type.
Text comparisons also depend on collation. Collation can control case sensitivity, accent sensitivity, language-specific ordering, and whether certain characters compare as equivalent. Two databases running the same-looking equality or ordering expression can therefore produce different results when their collations differ.
For stable behavior, define appropriate column collations, normalize data where the domain requires it, and avoid applying ad hoc case-changing functions to every search unless a matching expression index supports that design.
Why Equality Cannot Test NULL
-- Incorrect
WHERE completed_at = NULL
-- Correct
WHERE completed_at IS NULL
WHERE completed_at IS NOT NULL
NULL is not an ordinary value equal to itself. Equality and inequality comparisons with it return unknown. Use the dedicated NULL predicates or a documented null-safe comparison feature where required.
Logical Operators
AND, OR, and NOT combine or negate predicates.
WHERE account_status = 'ACTIVE'
AND balance_amount > 0
WHERE customer_type = 'BUSINESS'
OR lifetime_value >= 10000
WHERE NOT order_status = 'CANCELLED'
AND requires both conditions to be true. OR requires at least one true condition. NOT reverses true and false but leaves unknown as unknown.
Logical Precedence and Parentheses
A common precedence order places parentheses first, then NOT, AND, and OR. This predicate may not mean what a reader initially expects:
WHERE status = 'ACTIVE'
OR status = 'PENDING'
AND region_code = 'US'
Because AND normally binds first, the region condition applies only to pending rows. If it should apply to both statuses, group the alternatives:
WHERE (status = 'ACTIVE' OR status = 'PENDING')
AND region_code = 'US'
Three-Valued Logic with AND, OR, and NOT
The following results are important when nullable conditions are combined:
| Expression | Result | Reason |
|---|---|---|
TRUE AND UNKNOWN | Unknown | The second condition may be false |
FALSE AND UNKNOWN | False | Both cannot be true |
TRUE OR UNKNOWN | True | At least one is true |
FALSE OR UNKNOWN | Unknown | The second condition may be true |
NOT UNKNOWN | Unknown | Missing knowledge remains missing |
Evaluation Order Is Not Procedural
SQL text may look as though conditions execute from left to right, but the optimizer can simplify, reorder, or transform predicates while preserving SQL semantics. A first condition should not be used as a procedural guard for an unsafe second expression.
-- Do not depend on left-to-right short-circuit evaluation
WHERE input_text <> ''
AND CAST(input_text AS INTEGER) > 0
If the column can contain invalid numeric text, the conversion may still fail. Validate data when it enters the system, store it using the proper type, or use a documented safe-conversion function where the database provides one. The same principle applies to division, date parsing, and user-defined functions.
BETWEEN and NOT BETWEEN
BETWEEN tests an inclusive range.
WHERE total_amount BETWEEN 100 AND 500
WHERE score NOT BETWEEN 0 AND 100
The first condition includes exactly 100 and 500. It is equivalent in intent to total_amount >= 100 AND total_amount <= 500. Explicit comparisons can be clearer when different boundary rules are required.
BETWEEN with NULL and Reversed Bounds
If the tested value or either boundary is NULL, a normal BETWEEN predicate generally becomes unknown. The database does not treat a missing minimum as unbounded or a missing maximum as infinite. Optional boundaries must be expressed deliberately.
WHERE (:minimum_amount IS NULL OR total_amount >= :minimum_amount)
AND (:maximum_amount IS NULL OR total_amount <= :maximum_amount)
Standard asymmetric BETWEEN also expects the lower boundary first. BETWEEN 500 AND 100 does not automatically reorder its endpoints. Validate filter input so minimum and maximum values reflect the intended range.
Date and Timestamp Ranges
Inclusive upper boundaries can be misleading for timestamps. A date literal often represents midnight at the start of that date, not its final moment. A half-open range is usually safer:
WHERE ordered_at >= :start_time
AND ordered_at < :next_period_start
This includes every supported fractional second before the next period without inventing an end-of-day precision. Bind typed date or timestamp parameters and define time-zone behavior explicitly.
IN Operator
IN tests membership in a value list or subquery result.
WHERE order_status IN ('PAID', 'SHIPPED', 'DELIVERED')
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE region_code = 'US'
)
A short IN list is usually clearer than repeating equality comparisons joined with OR. Values should have compatible types with the tested expression.
IN Lists and Application Input
An empty parenthesized IN () list is invalid in many database systems. Applications generating optional filters should omit the predicate or deliberately produce an always-false condition rather than emitting invalid SQL.
Do not construct an IN list by joining unchecked user strings. Use the driver or framework's supported collection-parameter pattern, a temporary table, table-valued parameter, array feature, or one bound placeholder per value. The right technique depends on the database and expected list size.
Very large lists can increase parsing cost and produce unstable plans. Loading values into a relational structure and joining against it is often clearer for bulk matching.
NOT IN and the NULL Problem
NOT IN can behave unexpectedly when its list or subquery contains NULL.
WHERE customer_id NOT IN (
SELECT blocked_customer_id
FROM blocked_customers
)
If the subquery returns a null value, comparisons against that entry become unknown. The combined condition may reject every candidate rather than returning the expected nonmatching rows. Exclude nulls explicitly or use a correlated anti-existence test.
WHERE NOT EXISTS (
SELECT 1
FROM blocked_customers AS b
WHERE b.blocked_customer_id = c.customer_id
)
LIKE Pattern Matching
LIKE compares text against a pattern. Percent commonly matches any sequence of characters, including none, and underscore matches one character.
WHERE customer_name LIKE 'A%'
WHERE email_address LIKE '%@example.com'
WHERE product_code LIKE 'AB_12'
WHERE notes NOT LIKE '%obsolete%'
Case sensitivity depends on collation and database behavior. PostgreSQL provides ILIKE for case-insensitive pattern matching, but that operator is not portable.
LIKE Is Not a Regular Expression
LIKE supports a small wildcard language rather than full regular expressions. Percent and underscore have special meanings; symbols such as a period, plus, or parentheses are normally literal unless the database's pattern rules say otherwise.
Several products provide separate regular-expression operators or functions, but their syntax, flags, Unicode behavior, and index support differ. Use LIKE for simple prefix, suffix, and contains requirements. Choose a regular-expression feature only when the requirement needs that additional language.
Escaping LIKE Wildcards
If percent or underscore should be treated literally, use the product's supported escape syntax.
WHERE description LIKE '%10!%%' ESCAPE '!'
This example searches for text containing 10%. When users supply search text, bind it as a parameter and escape wildcard characters according to the intended search behavior.
Pattern Search Performance
A pattern beginning with a known prefix, such as 'ABC%', may use an appropriate index. A leading wildcard, such as '%ABC', often prevents a normal B-tree index from narrowing the search efficiently.
Large substring-search workloads may require full-text search, specialized indexes, or a search service. Confirm actual behavior with execution plans rather than assuming that every LIKE predicate is slow.
IS NULL and IS NOT NULL
WHERE manager_id IS NULL
WHERE shipped_at IS NOT NULL
These predicates test missing values. A null string is different from an empty string in most databases, although Oracle treats an empty character string as null in important contexts. Whitespace-only text is different again and may require trimming and validation.
EXISTS and NOT EXISTS
EXISTS is true when its subquery returns at least one row. The selected value is not important.
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.order_status = 'OPEN'
);
NOT EXISTS finds rows with no match. Existence tests express intent directly and let the optimizer stop looking after a qualifying row is found. Using COUNT(*) > 0 can request more work than the question requires.
EXISTS Compared with IN
IN asks whether a value belongs to a set. EXISTS asks whether a qualifying row exists. Optimizers may transform equivalent forms similarly, but NULL semantics and readability can differ.
Use the form that states the requirement accurately, then inspect the plan for important workloads. Prefer NOT EXISTS when an anti-match subquery can return nulls.
ANY, SOME, and ALL
Quantified comparison operators compare a value with the rows produced by a subquery. ANY and SOME are synonyms in the SQL standard: the comparison must be true for at least one value. ALL requires it to be true for every value.
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department_id = 20
)
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department_id = 20
)
The first condition means greater than at least one departmental salary. The second means greater than every departmental salary. Empty sets and null values require careful testing because they affect quantified truth rules.
Quantified Comparison Edge Cases
A comparison with ANY over an empty result has no value that can make the condition true, so it is false. A comparison with ALL over an empty result has no counterexample, so it is true. This logical behavior can surprise readers when a subquery unexpectedly returns no rows.
Nulls complicate the result. If no comparison is true for ANY but one is unknown, the result may be unknown. If no comparison is false for ALL but one is unknown, that result may also be unknown. Filter nulls when they are outside the domain, or model the missing-value requirement explicitly.
Use descriptive CTE names and comments around quantified comparisons. A rewrite using MIN, MAX, or EXISTS can sometimes be easier to understand, but it must preserve empty-set and NULL semantics.
String Concatenation Operators
String concatenation is highly dialect-dependent. Standard-style SQL and PostgreSQL commonly use ||, SQL Server commonly uses +, and many products provide CONCAT().
first_name || ' ' || last_name
CONCAT(first_name, ' ', last_name)
NULL behavior can differ between operators and functions. Use the target database's documented syntax and define whether a missing component should make the complete string null or be replaced.
Operators in UPDATE Assignments
UPDATE inventory
SET quantity_on_hand = quantity_on_hand - :sold_quantity
WHERE product_id = :product_id;
The equals sign in the SET clause assigns a new value; the equals sign in WHERE compares values. Context determines its role. Updates based on current values also require suitable transactions and concurrency controls.
Set Operators
Set operators combine complete query results rather than scalar values.
SELECT email_address FROM customers
UNION
SELECT email_address FROM prospects;
UNION removes duplicates, while UNION ALL preserves them and usually avoids duplicate-elimination work. INTERSECT returns common rows. EXCEPT returns rows from the first query that are absent from the second; Oracle uses MINUS for this operation.
Set Operator Requirements
Each query participating in a set operation must return the same number of columns, and corresponding columns must have compatible types. Output names normally come from the first query. A final ORDER BY applies to the combined result unless subqueries introduce separate supported ordering behavior.
Choose UNION ALL when duplicates are meaningful or impossible by design. Use duplicate-removing UNION only when set semantics are actually required.
Duplicate and NULL Semantics in Set Operations
Duplicate-removing set operators compare complete output rows. For this duplicate-elimination purpose, rows containing corresponding null values can be treated as duplicates even though ordinary equality with NULL is unknown. Set duplicate semantics and scalar comparison semantics are therefore not identical.
UNION ALL preserves every occurrence from every branch. UNION adds work to identify duplicate rows, commonly through sorting or hashing. That cost can be substantial when each branch returns many rows or wide columns.
Apply filters in individual branches when they logically belong there. Apply final ordering only after the set has been combined. Parentheses or derived tables may be needed when a branch requires its own limiting operation under the target dialect.
Bitwise Operators
Bitwise operators manipulate individual bits in integer values. Exact symbols differ by database, but bitwise AND commonly tests whether a flag is present.
WHERE permission_mask & 4 = 4
This can represent compact flags, but bitmasks reduce relational clarity and can complicate constraints and indexes. Separate relational rows or Boolean columns may be easier to query and govern. Use bitwise storage only when its tradeoffs are understood.
Operator Precedence Across Categories
A simplified mental model is arithmetic first, then comparison and predicate operations, then NOT, AND, and OR. Real precedence tables contain dialect-specific details for concatenation, bitwise operators, and other constructs.
WHERE quantity * unit_price > 100
AND status = 'ACTIVE'
OR priority_code = 'URGENT'
Parenthesize the intended business groups instead of relying on a long precedence chain. This protects both correctness and maintainability.
Operators with Different Data Types
An operator's behavior depends on operand types. Division between integers can differ from division involving decimals. The plus symbol may mean numeric addition or string concatenation in some dialects. Date subtraction may produce a number, interval, or unsupported operation.
Avoid ambiguous implicit conversions. Bind parameters using the target column type, cast deliberately where needed, and use database-specific date and interval features rather than treating dates as generic numbers.
Date, Time, and Interval Operators
Date arithmetic varies significantly across databases. One system may support adding an interval to a timestamp, another may require a date-add function, and another may interpret subtraction differently. The following standard-style expression is conceptual and must be checked against the target product:
scheduled_at + INTERVAL '30' MINUTE
Time-zone-aware and time-zone-naive values should not be mixed casually. Comparisons must represent the same timeline and precision. Daylight-saving transitions can make a local day shorter or longer than 24 hours, so calendar operations and elapsed-duration operations are not always interchangeable.
Operators with Boolean Columns
Databases with a native Boolean type may support direct predicates:
WHERE is_active
WHERE is_verified = TRUE
Other databases represent flags using numbers or characters. Do not assume that TRUE, 1, and 'Y' are interchangeable across products. Follow the schema's actual type and constraints.
Operators Inside CASE
CASE
WHEN total_amount >= 1000
AND customer_type = 'PREMIUM' THEN 'PRIORITY'
WHEN total_amount >= 100 THEN 'STANDARD'
ELSE 'SMALL'
END
Operators form the branch conditions, while CASE returns the selected value. Put specific or higher thresholds before broader branches so earlier matches do not hide later logic.
Operators in Aggregate and HAVING Expressions
SELECT customer_id,
SUM(quantity * unit_price) AS customer_total
FROM order_items
GROUP BY customer_id
HAVING SUM(quantity * unit_price) > 1000;
The multiplication operator calculates a value per row, SUM aggregates the results, and greater-than filters completed groups. A non-aggregate row filter belongs in WHERE when possible so fewer rows enter grouping.
Operators in JOIN Conditions
SELECT o.order_id, c.customer_name
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id;
Equality joins are common, but range and non-equality joins are also possible:
JOIN price_bands AS b
ON p.price >= b.minimum_price
AND p.price < b.maximum_price
Ensure ranges do not overlap unless multiple matches are intended. Indexes and statistics on join operands strongly affect performance.
Operators and Index Usage
A predicate is often called sargable when the database can use an index search efficiently. Direct comparisons and bounded ranges commonly support this:
WHERE created_at >= :start_time
AND created_at < :end_time
Applying a function or arithmetic expression to an indexed column can make a normal index less useful:
-- Potentially less index-friendly
WHERE YEAR(created_at) = 2026
-- Usually clearer for an index on created_at
WHERE created_at >= :year_start
AND created_at < :next_year_start
Expression indexes and generated columns may support transformed searches. Verify with execution plans and realistic data.
Negative Predicates and Index Strategy
Operators such as <>, NOT IN, and NOT LIKE often describe a large portion of a table. An index may provide less benefit when most rows qualify, although selectivity, covering columns, partitioning, and optimizer capabilities all influence the plan.
A positive business definition can sometimes be clearer and more selective. For example, enumerating a small set of active statuses may be better than excluding one inactive status, especially when new statuses can be added later. Do not rewrite solely for style; compare correctness and actual plans.
Parameterized Queries and Dynamic Operators
Bind values as parameters instead of concatenating them into SQL:
WHERE total_amount >= :minimum_amount
AND status = :status
Parameters normally represent values, not operator tokens, column names, directions, or arbitrary SQL fragments. If an application lets users choose an operator, map the choice to a small allowlist of known SQL templates. Never insert unchecked operator text into a statement.
Common Operator Mistakes
- Using
==instead of standard SQL equality. - Testing null with
= NULLor<> NULL. - Assuming
BETWEENexcludes either boundary. - Using
NOT INwith a nullable subquery. - Mixing
ANDandORwithout parentheses. - Confusing SQL
LIKEwildcards with regular-expression syntax. - Assuming pattern matching always ignores case.
- Forgetting integer division or a zero denominator.
- Assuming
<>includes rows where the column is null. - Comparing incompatible types and relying on implicit conversion.
- Comparing timestamps with ambiguous character literals.
- Wrapping indexed columns in avoidable functions.
Testing Operators Systematically
A strong operator test matrix includes ordinary values, exact boundaries, values just below and above each boundary, NULL, empty text where relevant, zero, negative numbers, maximum supported precision, and incompatible input that the application should reject.
For logical expressions, design rows that make each condition true, false, and unknown. For AND and OR, test every meaningful combination rather than only the successful path. For CASE, exercise every branch and the ELSE result.
Pattern tests should include literal percent and underscore characters, case variants, accents, Unicode characters, and long inputs. Date-range tests should include the exact start, exact end, the smallest supported instant before and after each boundary, leap days, and time-zone transitions when the system stores local times.
For set operators, test duplicates, null-containing rows, incompatible column types, and empty branches. For quantified comparisons, include empty subqueries and subqueries containing nulls.
Including NULL Explicitly
A not-equal predicate does not normally include null values because the comparison is unknown. If the requirement includes both other values and missing values, state both cases:
WHERE order_status <> 'CANCELLED'
OR order_status IS NULL
Whether missing status should be accepted is a business decision, not merely a SQL workaround.
Real-World Employee Query
SELECT employee_id,
employee_name,
salary + COALESCE(bonus_amount, 0) AS total_pay
FROM employees
WHERE employment_status = 'ACTIVE'
AND salary >= :minimum_salary
AND department_id IN (10, 20, 30)
ORDER BY total_pay DESC;
This query combines arithmetic, equality, range comparison, membership, NULL substitution, and descending ordering. The parameter should be bound using a type compatible with salary.
Real-World Product Query
SELECT product_id,
product_name,
list_price - COALESCE(discount_amount, 0) AS sale_price
FROM products
WHERE is_active = TRUE
AND stock_quantity > 0
AND category_code IN ('BOOK', 'COURSE')
AND product_name LIKE :name_prefix
ORDER BY sale_price, product_id;
A caller searching by prefix can bind a value such as 'SQL%'. The final product ID provides deterministic ordering when prices are equal.
Real-World Order Query
SELECT o.order_id,
o.customer_id,
o.total_amount
FROM orders AS o
WHERE o.ordered_at >= :period_start
AND o.ordered_at < :next_period_start
AND o.order_status NOT IN ('CANCELLED', 'DRAFT')
AND EXISTS (
SELECT 1
FROM order_items AS i
WHERE i.order_id = o.order_id
);
This example uses a half-open timestamp range, a controlled non-null status list, and an existence test for order items. If order_status can be null and should be included, add that rule explicitly.
Complex Business Rule Example
SELECT customer_id,
account_status,
lifetime_value
FROM customers
WHERE account_status = 'ACTIVE'
AND country_code IN ('US', 'CA')
AND (
customer_type = 'PREMIUM'
OR lifetime_value >= :priority_threshold
)
AND NOT EXISTS (
SELECT 1
FROM compliance_holds AS h
WHERE h.customer_id = customers.customer_id
AND h.released_at IS NULL
);
This combines equality, membership, grouped alternatives, numeric comparison, anti-existence, and a NULL predicate. The parentheses show that either premium type or sufficient lifetime value qualifies, while every qualifying customer must still be active, in an allowed country, and free of an unreleased hold.
Readable Operator Formatting
Place major conditions on separate lines, align related boundaries, and indent grouped alternatives.
WHERE account_status = 'ACTIVE'
AND created_at >= :start_time
AND created_at < :end_time
AND (
customer_type = 'PREMIUM'
OR lifetime_value >= 10000
)
Dense chains hide grouping mistakes. Readable formatting does not replace parentheses, but it makes their intended structure visible.
Operator Troubleshooting Checklist
- Confirm that the operator matches the intended operation and database dialect.
- Add parentheses around mixed logical or arithmetic groups.
- Test true, false, and unknown outcomes for nullable operands.
- Check operand types, precision, collation, and implicit conversion.
- Verify case sensitivity and wildcard escaping for pattern searches.
- Inspect nullable values before using
NOT IN. - Use half-open boundaries for timestamp periods where appropriate.
- Guard division only when an undefined null result is meaningful.
- Use parameters for values and allowlists for dynamic SQL structure.
- Inspect the execution plan for important search and join predicates.
Interview Questions and Answers
What is an SQL operator? It is a symbol or keyword that performs an operation on one or more operands inside an expression.
What are the main operator groups? Arithmetic, comparison, logical, range, membership, pattern, NULL, existence, quantified comparison, concatenation, set, and bitwise operators.
Why does = NULL not work? A comparison with missing information is unknown; use IS NULL.
Why can NOT IN be dangerous? A null returned by its subquery can make the overall result unknown for every candidate.
What is the difference between UNION and UNION ALL? UNION removes duplicates, while UNION ALL preserves them.
What is a sargable operator condition? It is a predicate shaped so the optimizer can efficiently search an available index, such as a direct comparison or bounded range on the indexed column.
Does SQL always evaluate conditions left to right? No. The optimizer can transform and reorder expressions while preserving SQL semantics, so code should not depend on procedural short-circuit order.
Key Takeaway
SQL operators are the working vocabulary inside expressions. Arithmetic operators calculate values; comparison and logical operators build predicates; range, membership, pattern, NULL, existence, and quantified operators express richer conditions; and set operators combine query results.
OPERANDS + OPERATOR + SQL EVALUATION RULES = RESULT
Correct use requires more than remembering symbols. Account for data types, precedence, NULL and three-valued logic, timestamp boundaries, dialect differences, parameter safety, and index behavior. Clear parentheses, typed parameters, explicit missing-value rules, and execution-plan checks turn operator knowledge into reliable SQL.