Expression & Operators

MySQL Expressions

MySQL allows you to write expressions that include constants, function calls, and references to table columns.

These values can be combined using different kinds of operators, such as arithmetic or comparison operators, and terms of an expression can be grouped with parentheses. Expressions occur most commonly in the output column list and WHERE clause of SELECT statements. For example, the following:

SELECT CONCAT(last_name, ', ', first_name), (YEAR(death) - YEAR(birth)) - IF(RIGHT(death,5) < RIGHT(birth,5),1,0) FROM president WHERE birth > '1900-1-1' AND DEATH IS NOT NULL;

Each column selected represents an expression, as does the content of the WHERE clause. Expressions also occur in the WHERE clause of DELETE and UPDATE statements, the VALUES() clause of INSERT statements, and so on.

When MySQL encounters an expression, it evaluates it to produce a result. For example, (4*3)/(4-2) evaluates to the value 6. Expression evaluation may involve type conversion, such as when MySQL converts the number 960821 into a date '1996-08-21' if the number is used in a context requiring a DATE value.

Writing Expressions

An expression can be as simple as a single constant:

0 Numeric constant 'abc' String constant

Expressions can use function calls. Some functions take arguments (values inside the parentheses) and some do not. Multiple arguments should be separated by commas.

NOW() Function with no arguments STRCMP('abc','def') Function with two arguments STRCMP( 'abc', 'def' ) Spaces around arguments are legal STRCMP ('abc','def') Space after function name is illegal

If there is a space after the function name, the MySQL parser may interpret the function name as a column name. (Function names are not reserved words, and you can use them for column names if you want.) The usual result is a syntax error.

You can use table column values in expressions. In the simplest case, when the table to which a column belongs is clear from context, a column reference can be given simply as the column name. Only one table is named in each of the following SELECT statements, so the column references are unambiguous, even though the same column names are used in each statement:

SELECT last_name, first_name FROM associate; SELECT last_name, first_name FROM member;

If it's not clear which table should be used, column names can be preceded by the table name. If it's not clear which database should be used, the table name can be preceded by the database name. You can also use these more-specific forms in unambiguous contexts if you simply want to be more explicit:

SELECT associate.last_name, president.first_name, member.last_name, member.first_name FROM president, member WHERE associate.last_name = member.last_name; SELECT sampdb.student.name FROM sampdb.student;

Finally, you can combine all these kinds of values (constants, function calls, and column references) to form more complex expressions.