MySQL provides you with some other operators for using in the WHERE clause to form complex search conditions such as:
BETWEEN – Selects values within a range of values.
LIKE – Matches values based on pattern matching.
IN – Specifies if a value matches any value in a set.
IS NULL – Checks if the value in NULL.
BETWEEN Operator
The BETWEEN operator allows you to specify a range to test. The following illustrates he syntax of the BETWEEN operator:
expr BETWEEN begin_expr AND end_expr;
The expr is the expression to test in the range that is defined by begin_expr and end_expr. All three expressions: expr, begin expr, and end_expr must have the same data type.
The BETWEEN operator returns true if the value of the expr is greater than on equal to (>=) the value of begin_expr and less than or equal to (<=) the value of the end_expr otherwise it returns zero.If any expression is NULL, the BETWEEN operator returns a NULL value.
Example:
SELECT FROM student WHERE total fees between 7000 and 9000
Will displays all the students whose total fees is greater than or equals to 7000 and less than or equals to 9000.
LIKE Operator
The like operator is commonly used to select data based on patterns. Using the LIKE operator in the right way is essential to increase the query performance.
The LIKE operator allows you to select data from a table based on a specified pattern. Therfore, the LIKE operator is often used in the WHERE clause of the select statement.
MySQL provides two wildcard characters for using with the LIKE operator, the percentage % and underscore_. The percentage (%) wildcard allows you to match any string of zero or more characters. The underscore ( _ ) wildcare allows you to match any single character.
Look at the following example, to display all the students whose name starts with ‘M’,
Select * From student where student_name like 'M%'
Suppose, we want to display all products whose product id starts with ‘100’ and has any number at the 4th position, we can write the query as
Select * from products where productid like '100_'
IN Operator :
The IN operator allows you to determine if a specified value matches any value in a set of values.
Syntax:
SELECT column1, column2,.. FROM table_name WHERE(column1)IN ('value1','value2',..)
Let’s see the query in detail:
You can use a column or an expression ( expr ) with the IN operator in the WHERE clause. The values in the list are separated by commas (,). The IN operator can also be used in the WHERE clause of other statements such as UPDATE, and DELETE
You can combine the IN operator with the NOT operator to determine if a value does not match any value in a list.
The following example will display all the students, who were not joined in the ‘DCA’ and ‘ADJP’ course.
SELECT * FROM student WHERE course not in ('ADJP','DCA')
NULL as a VALUE :
In simple terms, NULL is simply a place holder for data that does not exist. When performing insert operations on tables, they will be times when some field values will not be available. In such situations, we can use NULL as the place holder for the values that have not been submitted. Now we execute following query and then Click Browse tab at the top.
insert into student (enroll_no,student_name) values (1011, 'POORINMA')
It shows how NULL values look in database.
Now we want the display all the rows where gender column has a null, we can execute the following query,
SELECT * FROM `student` WHERE gender is null
Let’s also look at some of the basics for NULL,
- NULL is not a data type – this means it is not recognized as an “int”, “date” or any other defined data type.
- Arithmetic operations involving NULL always return NULL for example, 86 + NULL = NULL.
Note: Update the null columns with relevant data.