C1

Where Clause Operators

Used for conditions that includes strings.

e.g.

SELECT product_code, product_desc
FROM product
WHERE product_code LIKE 'D%'
ORDER BY 2;

The above code will filter for product_code which starts with 'D'

The % sign acts as a wild card that matches 0 or more characters

_ matches exactly one character

\ is an escape character (to escape the wild_card characters)

Common patterns:

Pattern	Meaning / Matches
`'abc%'`	Matches strings starting with `abc`
`'%xyz'`	Matches strings ending with `xyz`
`'%mid%'`	Matches strings that contain `mid`
`'a_c'`	3-letter string with `a`, then any one character, then `c`
`'___'`	Matches strings that are exactly 3 characters
`'1%'`	Matches strings starting with `1`
`'%\%%'`	Matches strings that contain `%` (escaped)
`'%\_%'`	Matches strings that contain `_` (escaped)
`'%abc' COLLATE utf8mb4_general_ci`	Case-insensitive match: ends with `abc`
`'abc%' COLLATE utf8mb4_bin`	Case-sensitive match: starts with `abc`

Used to check for undefined columns and values

e.g.

SELECT product_code, product_desc
FROM product
WHERE premium IS NULL
ORDER BY 2 DESC;

The above code will get products where the premium status is NULL.

Unary Operator that flips value from TRUE to FALSE

NOTE: NOT NULL is still NULL

syntax:

IF(<condition>, <value if True> , <value if False>)

e.g.

SELECT Name, IF(Score>50, 'Pass' , 'Fail) AS Grade
FROM Score

The above code will display 'pass' or 'fail' under the Grade Column depending on the Score.