Expressions in SQL
Expressions are composed of various operators and operands that evaluate to a single value or a set of values. Some expressions do not use operators; for example, a column name is an expression. Expressions are used in many contexts, such as specifying values to be retrieved (in a SELECT clause) or compared (in a WHERE clause).
In the following example, empname and empage are expressions representing the column values to be retrieved, salary is an expression representing a column value to be compared, and 75000 is an integer literal expression.
SELECT empname, empage FROM employee
WHERE salary > 75000
Expressions that contain aggregate functions can appear only in SELECT lists and HAVING clauses unless they are within a nested SELECT clause. Aggregate functions cannot be nested.
An expression can be enclosed in parentheses without affecting its value.
A row value expression is a list of expressions, separated by commas and enclosed in parentheses. Row value expressions can be used with most comparison predicates and allow for row result comparisons. For example, this WHERE clause:
WHERE empname ='Jones' AND empage = 36 AND salary = 45000
can be written using a row value comparison as follows:
WHERE (empname, empage, salary)=('Jones', 36, 45000)
To test if there is at least one row in employees that has a 36 year old Jones can be achieved with either of the following examples, first written with an EXISTS predicate and then with a row value comparison:
WHERE EXISTS(SELECT 1 FROM employee WHERE empname='Jones' AND empage=36)
WHERE ('Jones', 36) IN (SELECT empname, empage FROM employee)
Last modified date: 04/03/2024