MySQL/Language/Operators

From Wikibooks, open books for an open world
Jump to navigation Jump to search

MySQL uses some standard SQL operators and some non-standard operators. They can be used to write expressions which involve constant values, variables, values contained in fields and / or other expressions.

Comparison operators[edit | edit source]

Equality[edit | edit source]

If you want to check if 2 values are equal, you must use the = operator: 

 SELECT True = True -- returns 1
 SELECT True = False -- returns 0

If you want to check if 2 values are different, you can use the <> or != operators, which have the same meaning: 

 SELECT True <> False -- returns 1
 SELECT True != True -- returns 0

<> return 1 where = returns 0 and vice versa.

IS and NULL-safe comparison[edit | edit source]

When you compare a NULL value with a non-NULL value, you'll get NULL. If you want to check if a value is null, you can use IS: 

 SELECT (NULL IS NULL) -- returns 1
 SELECT (1 IS NULL) -- returns 0
 SELECT (True IS True) -- returns an error!

You can check if a value is non-NULL: 

 SELECT (True IS NOT NULL) -- returns 1

There is also an equality operator which considers NULL as a normal value, so it returns 1 (not NULL) if both values are NULL and returns 0 (not NULL) if one of the values is NULL: 

 SELECT NULL <=> NULL -- 1
 SELECT True <=> True -- 1
 SELECT col1 <=> col2 FROM myTable

There is not a NULL-safe non-equality operator, but you can type the following: 

 SELECT NOT (col1 <=> col2) FROM myTable

IS and Boolean comparisons[edit | edit source]

IS and IS NOT can also be used for Boolean comparisons. You can use them with the reserved words TRUE, FALSE and UNKNOWN (which is merely a synonym for NULL). 

 SELECT 1 IS TRUE -- returns 1
 SELECT 1 IS NOT TRUE -- returns 0
 SELECT 1 IS FALSE -- returns 0
 SELECT (NULL IS NOT FALSE) -- returns 1: unknown is not false
 SELECT (NULL IS UNKNOWN) -- returns 1
 SELECT (NULL IS NOT UNKNOWN) -- returns 0

Greater, Less...[edit | edit source]

You can check if a value is greater than another value: 

 SELECT 100 > 0 -- returns 1
 SELECT 4 > 5 -- return 0

You can also check if a value is minor than another value: 

 SELECT 1 < 2 -- returns 1
 SELECT 2 < 2 -- returns 0

This kind of comparisons also works on TEXT values: 

 SELECT 'a' < 'b' -- returns 1

Generally speaking, alphabetical order is used for TEXT comparisons. However, the exact rules are defined by the COLLATION used. A COLLATION defines the sorting rules for a given CHARACTER SET. For example, a COLLATION may be case-sensitive, while another COLLATION may be case-insensitive.

You can check if a value is equal or greater than another value. For example, the following queries have the same meaning: 

 SELECT `a` >= `b` FROM `myTable`
 SELECT NOT (`a` < `b`) FROM `myTable`

Similarly, you can check if a value is less or equal to another value: 

 SELECT `a` <= `b` FROM `myTable`

BETWEEN[edit | edit source]

If you want to check if a value is included in a given range (boundaries included), you can use the BETWEEN ... AND ... operator. AND doesn't have its usual meaning. Example: 

 SELECT 2 BETWEEN 10 AND 100    -- 0
 SELECT 10 BETWEEN 10 AND 100   -- 1
 SELECT 20 BETWEEN 10 AND 100   -- 1

The value after BETWEEN and the value after AND are included in the range.

You can also use NOT BETWEEN to check if a value is not included in a range: 

 SELECT 8 NOT BETWEEN 5 AND 10 -- returns 0

IN[edit | edit source]

You can use the IN operator to check if a value is included in a list of values: 

 SELECT 5 IN (5, 6, 7) -- returns 1
 SELECT 1 IN (5, 6, 7) -- returns 0

You should not include in the list both numbers and strings, or the results may be unpredictable. If you have numbers, you should quote them: 

 SELECT 4 IN ('a', 'z', '5')

There is not a theoretical limit to the number of values included in the IN operator.

You can also use NOT IN: 

 SELECT 1 NOT IN (1, 2, 3) -- returns 0

Logical operators[edit | edit source]

MySQL Boolean logic[edit | edit source]

MySQL doesn't have a real BOOLEAN datatype.

FALSE is a synonym for 0. Empty strings are considered as FALSE in a Boolean context.

TRUE is a synonym for 1. All non-NULL and non-FALSE data are considered as TRUE in a boolean context.

UNKNOWN is a synonym for NULL. The special date 0/0/0 is NULL.

NOT[edit | edit source]

NOT is the only operator which has only one operand. It returns 0 if the operand is TRUE, returns 1 if the operand is FALSE and returns NULL if the operand is NULL. 

 SELECT NOT 1 -- returns 0
 SELECT NOT FALSE -- returns 1
 SELECT NOT NULL -- returns NULL
 SELECT NOT UNKNOWN -- returns NULL

! is a synonym for NOT. 

 SELECT !1

AND[edit | edit source]

AND returns 1 if both the operands are TRUE, else returns 0; if at least one of the operands is NULL, returns NULL. 

 SELECT 1 AND 1 -- returns 1
 SELECT 1 AND '' -- return 0
 SELECT '' AND NULL -- returns NULL

&& is a synonym for AND. 

 SELECT 1 && 1

OR[edit | edit source]

OR returns TRUE if at least one of the operands is TRUE, else returns FALSE; if the two operands are NULL, returns NULL. 

 SELECT TRUE OR FALSE -- returns 1
 SELECT 1 OR 1 -- returns 1
 SELECT FALSE OR FALSE -- returns 0
 SELECT NULL OR TRUE -- returns NULL

|| is a synonym for OR. 

 SELECT 1 || 0

XOR[edit | edit source]

XOR (eXclusive OR) returns 1 if only one of the operands is TRUE and the other operand is FALSE; returns 0 if both the operands are TRUE o both the operands are FALSE; returns NULL if one of the operands is NULL. 

 SELECT 1 XOR 0 -- returns 1
 SELECT FALSE XOR TRUE -- returns 1
 SELECT 1 XOR TRUE -- returns 0
 SELECT 0 XOR FALSE -- returns 0
 SELECT NULL XOR 1 -- returns NULL

Synonyms[edit | edit source]

  • AND can be written as &&
  • OR can be written ad ||
  • NOT can be written as !

Only NOT (usually) has a different precedence from its synonym. See operator precedence for detail.

Arithmetic operators[edit | edit source]

MySQL supports operands which perform all basic arithmetic operations.

You can type positive values with a '+', if you want: 

 SELECT +1 -- return 1

You can type negative values with a '-'. - is an inversion operand: 

 SELECT -1 -- returns -1
 SELECT -+1 -- returns -1
 SELECT --1 -- returns 1

You can make sums with '+': 

 SELECT 1 + 1 -- returns 2

You can make subtractions with '-': 

 SELECT True - 1 -- returns 0

You can multiply a number with '*': 

 SELECT 1 * 1 -- returns 1

You can make divisions with '/'. Returns a FLOAT number: 

 SELECT 10 / 2 -- returns 5.0000
 SELECT 1 / 1 -- returns 1.0000
 SELECT 1 / 0 -- returns NULL (not an error)

You can make integer divisions with DIV. Resulting number is an INTEGER. No remainder. This has been added in MySQL 4.1. 

 SELECT 10 DIV 3 -- returns 3

You can get the remainder of a division with '%' or MOD: 

 SELECT 10 MOD 3 -- returns 1

Using + to cast data[edit | edit source]

You can convert an INTEGER to a FLOAT doing so: 

 SELECT 1 + 0.0 -- returns 1.0
 SELECT 1 + 0.000 -- returns 1.000
 SELECT TRUE + 0.000 -- returns 1.000

You can't convert a string to a FLOAT value by adding 0.0, but you can cast it to an INTEGER: 

 SELECT '1' + 0 -- returns 1
 SELECT '1' + FALSE -- returns 1
 SELECT <nowiki>''</nowiki> + <nowiki>''</nowiki> -- returns 0

Text operators[edit | edit source]

There are no concatenation operators in MySQL.

Arithmetic operators convert the values into numbers and then perform arithmetic operations, so you can't use + to concatenate strings.

You can use the CONCAT() function instead.

LIKE[edit | edit source]

The LIKE operator may be used to check if a string matches to a pattern. A simple example: 

 SELECT * FROM articles WHERE title LIKE 'hello world'

The pattern matching is usually case insensitive. There are two exceptions:

  • when a LIKE comparison is performed against a column which has been declared with the BINARY flag (see CREATE TABLE);
  • when the expression contains the BINARY clause:
 SELECT * 'test' LIKE BINARY 'TEST' -- returns 0

You can use two special characters for LIKE comparisons:

  • _ means "any character" (but must be 1 char, not 0 or 2)
  • % means "any sequence of chars" (even 0 chars or 1000 chars)

Note that "\" also escapes quotes ("'") and this behaviour can't be changed by the ESCAPE clause. Also, the escape character does not escape itself.

Common uses of LIKE:

  • Find titles starting with the word "hello":
 SELECT * FROM articles WHERE title LIKE 'hello%'
  • Find titles ending with the word "world":
 SELECT * FROM articles WHERE title LIKE '%world'
  • Find titles containing the word "gnu":
 SELECT * FROM articles WHERE title LIKE '%gnu%'

These special chars may be contained in the pattern itself: for example, you could need to search for the "_" character. In that case, you need to "escape" the char: 

 SELECT * FROM articles WHERE title LIKE '\_%' -- titles starting with _
 SELECT * FROM articles WHERE title LIKE '\%%' -- titles starting with %

Sometimes, you may want to use an escape character different from "\". For example, you could use "/": 

 SELECT * FROM articles WHERE title LIKE '/_%' ESCAPE '/'

When you use = operator, trailing spaces are ignored. When you use LIKE, they are taken into account. 

 SELECT 'word' = 'word ' -- returns 1
 SELECT 'word' LIKE 'word ' -- returns 0


LIKE also works with numbers. 

 SELECT 123 LIKE '%2%' -- returns 1

If you want to check if a pattern doesn't match, you can use NOT LIKE: 

 SELECT 'a' NOT LIKE 'b' -- returns 1

SOUNDS LIKE[edit | edit source]

You can use SOUNDS LIKE to check if 2 text values are pronounced in the same way. SOUNDS LIKE uses the SOUNDEX algorithm, which is based on English rules and is very approximate (but simple and thus fast). 

 SELECT `word1` SOUNDS LIKE `word2` FROM `wordList` -- short form
 SELECT SOUNDEX(`word1`) = SOUNDEX(`word2`) FROM `wordList` -- long form

SOUNDS LIKE is a MySQL-specific extension to SQL. It has been added in MySQL 4.1.

Regular expressions[edit | edit source]

You can use REGEXP to check if a string matches to a pattern using regular expressions. 

 SELECT 'string' REGEXP 'pattern'

You can use RLIKE as a synonym for REGEXP.

Bitwise operators[edit | edit source]

Bit-NOT: 

 SELECT ~0 -- returns 18446744073709551615
 SELECT ~1 -- returns 18446744073709551614

Bit-AND: 

 SELECT 1 & 1 -- returns 1
 SELECT 1 & 3 -- returns 1
 SELECT 2 & 3 -- returns 2

Bit-OR: 

 SELECT 1 | 0 -- returns 1
 SELECT 3 | 0 -- returns 3
 SELECT 4 | 2 -- returns 6

Bit-XOR: 

 SELECT 1 ^ 0 -- returns 1
 SELECT 1 ^ 1 -- returns 0
 SELECT 3 ^ 1 -- returns 2

Left shift: 

 SELECT 1 << 2 -- returns 4

Right shift: 

 SELECT 1 >> 2 -- 0

Conditions[edit | edit source]

IF[edit | edit source]

The structure IF ... THEN ... ELSE ... END IF; only functions in the stored procedures. To manage a condition out of them, we can use[1]: IF(condition, ifTrue, ifFalse);.

Example: SELECT IF(-1 < 0, 0, 1); returns 0.

Example with several conditions (switch)[2][3]: 

    IF n > m THEN SET s = '>';
    ELSEIF n = m THEN SET s = '=';
    ELSE SET s = '<';
    END IF;

CASE[edit | edit source]

SELECT CASE WHEN condition THEN ifTrue ELSE ifFalse END;

Example: SELECT CASE WHEN '-1 < 0' THEN 0 ELSE 1 END; renvoie 0.

Example with several conditions[4]: 

    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE
        BEGIN
        END;
    END CASE;

In one request: 

SELECT CASE v
    WHEN 1 THEN 'a'
    WHEN 2 THEN 'b'
    WHEN 3 THEN 'c'
    WHEN 4 THEN 'd'
    ELSE 0
    END as value

Precedence[edit | edit source]

Operator precedence[edit | edit source]

Table of operator precedence: 

 INTERVAL
 BINARY, COLLATE
 !
 - (unary minus), ~ (unary bit inversion)
 ^
 *, /, DIV, %, MOD
 -, +
 <<, >>
 &
 |
 =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
 BETWEEN, CASE, WHEN, THEN, ELSE
 NOT
 &&, AND
 XOR
 ||, OR
 :=

Modifiers:

  • PIPES_AS_CONCAT - If this SQL mode is enabled, || has precedence on ^, but - and ~ have precedence on ||.
  • HIGH_NOT_PRECEDENCE - If this SQL mode is enabled, NOT has the same precedence level as !.

Use of parenthesis[edit | edit source]

You can use parenthesis to force MySQL to evaluate a subexpression before another independently from operator precedence: 

 SELECT (1 + 1) * 5 -- returns 10

You can also use parenthesis to make an expression more readable by humans, even if they don't affect the precedence: 

 SELECT 1 + (2 * 5) -- the same as 1 + 2 * 5

Assignment operators[edit | edit source]

You can use the = operator to assign a value to a column: 

 UPDATE `myTable` SET `uselessField`=0

When you want to assign a value to a variable, you must use the := operator, because the use of = would be ambiguous (is it as assignment or a comparison?) 

 SELECT @myvar := 1

You can also use SELECT INTO to assign values to one or more variables.

  1. ↑ http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html
  2. ↑ https://dev.mysql.com/doc/refman/5.7/en/if.html
  3. ↑ https://dev.mysql.com/doc/refman/5.7/en/case.html
  4. ↑ https://dev.mysql.com/doc/refman/5.7/en/case.html
Retrieved from "https://en.wikibooks.org/w/index.php?title=MySQL/Language/Operators&oldid=3757530"