SQL Operators: BETWEEN, IN, LIKE and IS NULL

Hello students, welcome to Kwickprep. Why does where marks equals NULL return nothing, even when a student has no marks? Today we will learn four special operators that make where shorter and smarter. They are between, in, like and is NULL.

All our examples use one table called student. It has five columns: roll, name, section, marks and city. Kabir was absent for the test, so his marks are NULL. NULL means no value at all. It is not zero, and it is not a blank space.

Between checks whether a value lies in a range. We write the lower value first, then and, then the higher value. Between sixty five and eighty eight includes both end values. So Neha with exactly sixty five and Riya with exactly eighty eight are both kept.

Remember four points about between. It means exactly the same as greater than or equal to sixty five and less than or equal to eighty eight. Both end values are included, and exams test this often. Write the smaller value first, or you will get no rows. Not between gives the values outside the range, so here it would give only Meera.

In checks whether a value matches any item in a list. The list goes in round brackets, with commas between items. City in Pune, Delhi finds every student from Pune or Delhi. That is Aman, Kabir and Meera.

Here is why in is useful. It is a short form of many conditions joined with or. It works with numbers too, so roll in one, three, five gives Riya, Neha and Meera. Not in keeps the values that are not in the list, so city not in Pune, Delhi gives Riya and Neha.

Like matches text with a pattern, and the pattern uses wildcards. A wildcard is a symbol that stands for unknown characters. The percent sign matches any number of characters, even zero. The underscore matches exactly one character.

Percent a means any characters, then the letter a at the end. So this finds names ending in a. Riya, Neha and Meera match. In the same way, R percent finds names starting with R, and percent ee percent finds names containing ee anywhere.

Underscore e percent means exactly one character, then e, then anything. So the second letter must be e. Neha and Meera match. Pause and predict. How many names match four underscores? Only Riya, Neha and Aman, because each has exactly four letters. In MySQL, like usually ignores capital and small letters.

Now the question from the start. NULL means the value is unknown. Any comparison with an unknown value is also unknown, never true. So marks equals NULL finds no rows, and it gives no error either. This silent mistake is very common in exams.

The correct way is is NULL. Where marks is NULL finds Kabir, whose marks are missing. Is not NULL does the opposite. It would give the other four students, whose marks are filled in.

These operators work together with and, or and not. Here the city must be Surat or Pune. The marks must not be NULL. And the name must end in a. Aman fails the last check, so we get Riya, Neha and Meera.

Here is a table to help you choose. For a range of values, use between. To match one of several values, use in. To match a text pattern, use like with wildcards. To find missing values, use is NULL, never equals NULL.

Let us revise what we learned today. Between includes both end values. In is a short form of many or conditions. With like, percent matches any characters and underscore matches exactly one. Equals NULL finds nothing, so always use is NULL or is not NULL. And not works with all of them, like not between, not in and not like. Try each operator on your own table and predict the rows first.