Operators in SQL: BETWEEN, LIKE, IN, IS NULL,NOT NULL

 

SQL Operators :

There are different types of operators that can be used in SQL statements. These operators can be logical, Relational, ANY, ALL, IS_NULL, IS NOT NULL, LIKE, IN, and BETWEEN Operator. As we have discussed Relational and Logical Operators. Here we will discuss the rest of operators.

BETWEEN Operator:

This Operator is also called RANGE SEARCH CONDITION Operator because it is used to retrieve the range of data between two values. We can give specify the upper and lower bound of data in the condition and resultant data would be between these bounds. It is used with the WHERE Clause. Its negated version is NOT BETWEEN that inverse the results of BETWEEN Operator.

Syntax Of BETWEEN Operator:

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value 1 AND value 2 ;

Let suppose there is a “Student” TABLE.

Example:

So if we want to extract the data of those students who have got Marks between 60 and 90 the query will be.

Result:

This Operator doesn’t have much indicative power in SQL because the same functionality can be performed using compound condition (where two or more conditions are given).so we can write the above query like this:

The result will be the same.

IN/NOT IN Operator:

This Operator is also called SET MEMBERSHIP SEARCH CONDITION. It is used to find the exact match of data values in a specified table. It can be used with any type of data.

Syntax Of IN/NOT IN Operator:

SELECT column_name

FROM table_name

WHERE column_name IN ( value· 1, value· 2, ...ValueN);

N is the variable number of values.

Example:

If we want to retrieve the data of those students who have subject DS and DBMS we will write the exact strings like this:

Result:

As you can see the data of students with CS subject is not shown here.

It’s negated version is NOT IN, that is used to show the  results with those data values that are not matched with the specified values in the condition. Like if we don’t want to show the data of those students who have subjects DS and CS the query will be:

Result:

Now there are inverse results of IN operator that are shown here.

The logical operator OR can be used instead of this Operator if there is less number of values to be checked. so we can use OR operator instead of IN operator in the upper defined query. However, in the case of more values the use of IN Operator is recommended. Like in this case:

Result:

Here is, Only data of those students (that is exactly matched with the specified list of IN OPERATOR) is retrieved and the data of the rest of the students is not accessed.

LIKE Operator:

It is also called Pattern match search condition, because it is used to search for the pattern in a precise column. Pattern matching means to find out whether the certain series of characters exist in a specified column.

There are some important points about LIKE Operator:

“-“  underscore is used to depict a single character like if you write LIKE ‘_ _ _ _’ So here is 4 underscores which any data value that consists of 4 values that (would be an alphabet or digit) will be extracted.

LIKE ‘Z_ _ _’ this means the first character must be an alphabet Z and the rest of word would have 3 characters as there are 3 underscores.

“ % “ symbol is used for Wild-card Search. wild-card search means that you just have to specify the starting, middle, or ending  characters, and rest of the characters are uncertain like if you don’t know the whole address of employees you just know the city name London which is the middle name of Address column then you can apply wild-card at the starting and ending like this LIKE %London%.

LIKE %r means the starting string can be of any length but the last character must be an alphabet r.

The operator that is used to inverse the LIKE Operator is NOT LIKE, so if we use the NOT LIKE G% then it will show the results to which the first character is not G. It will not show the city “Glasgow” (as its first character is G) if we apply LIKE operator on city column.

Syntax Of LIKE Operator:

SELECT column· 1 , column· 2 , . . .
FROM table_name
WHERE columnN LIKE pattern;

Example:

If we want to extract the data of those students whose name starts with G and we don’t know the rest number of the characters then the query will be:

Result:

Note: In some DBMSs the * is used as wildcard character symbol instead of % Like Microsoft Access.

 

IS NULL/NOT NULL Operator:

What is Null value ? NULL denotes the value is inapplicable. It must be distinct from Blank space or zero. NULL shows that the data doesn’t exist in the database and missing So can’t be processed in any case. If we try to compare any value with Null then result can’t be true or false instead it would be unknown.

In some situations if we use the NULL comment in the condition like this

WHERE Std_Name= smith AND Marks = ‘  ‘ ;

This condition will not work because NULL comment in the condition is contemplated as an unknown value. So it can not be tested whether it is equal or not equal to the other string. If we use OR instead of And, because of thinking that it may work on the bases of either any condition will be true then we get an empty table as a result. So in these types of cases, we can use the NULL Operator definitely.

Syntax Of IS NULL:

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

Syntax Of IS NOT NULL Operator:

SELECT column_names

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Example:

If we want to extract the data of those students who have Null Marks the query will be:

Result: