WHERE, AND, OR, IN, NOT IN, BETWEEN, LIMIT, LIKE are filter data operators
- WHERE clause to filter rows based on specified conditions.
- SELECT lastname, firstname, jobtitle FROM employees WHERE jobtitle = 'Sales Rep';
- where employee_name = 'Alex' and age > 20
- where employee_name ='Alex' or age > 40
- The IN operator allows you to determine if a specified value matches any one of a list or a subquery.
- where employee_name in ('Alex','Roy')
- where employee_name not in ('Alex','Roy' )
- The BETWEEN operator allows you to specify a range to test. We often use the BETWEEN operator in the WHERE clause of the SELECT, INSERT, UPDATE, and DELETE statements.
- where employee_id between 2 and 4
- The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set
- SELECT column1,column2,... FROM table LIMIT offset , count;
- The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
- The count specifies the maximum number of rows to return.
- For wildcard filtering "Like" operator is used. Within a search string, % means match any number of occurrences of any character
- where employee_name like 'J%' (filter name start with J)
- where employee_name like '%a%' (filter all names containing letter a)
- where employee_name like '%y' (all names ending with letter y)
- To test whether a value is NULL or not, you use the IS NULL operator. of the IS NULL operator: value IS NULL
- WHERE salesrepemployeenumber IS NULL
- WHERE salesrepemployeenumber IS NOT NULL