Buzz
SQL employs various comparison keywords to enhance search capabilities, including 'BETWEEN...AND', 'ISNULL', 'LIKE', and 'IN'. This article aims to enlighten you on comparison operators BETWEEN...AND, IS NULL, LIKE, IN in SQL.
Here's a rundown of SQL comparison operators: BETWEEN...AND, IS NULL, LIKE, IN
Understanding SQL's LIKE Operator
The LIKE operator in SQL is used to list all rows in a table that match a specified pattern. It's particularly useful when you want to search for rows that match a specific pattern or if you're unsure of the entire value. In this case, you can use the wildcard character %.
Example of the LIKE Operator in SQL
Example 1
To select all students in a class whose name starts with the letter 'S', the LIKE statement is written as follows:
SELECT first_name, last_name
FROM student_details
WHERE first_name LIKE 'S%';
The output appears as follows:
The SELECT statement above searches for all rows in the name column with names starting with the letter S and followed by any characters.
Additionally, you can use the underscore (_) wildcard with the LIKE operator in SQL. In the search string, underscores represent a single character.
Example 2
To display all names starting with the letter 'a', the LIKE statement in SQL is written as follows:
SELECT first_name, last_name
FROM student_details
WHERE first_name LIKE '_a%';
The output appears as follows:
Some notes on the LIKE operator in SQL
An underscore serves as a placeholder for a single character. Therefore, you can use multiple underscores.
For example: '__i%' has 2 underscores on the left or 'S__j%' has two underscores between the characters 'S' and 'j'.
The BETWEEN ... AND Operator in SQL
The BETWEEN ... AND operator in SQL is used to compare data for a range of values.
Example of the BETWEEN ... AND Operator in SQL
To find the names of students aged between 10 and 15, the BETWEEN ... AND statement in SQL is written as follows:
SELECT first_name, last_name, age
FROM student_details
WHERE age BETWEEN 10 AND 15
The output appears as follows:
The IN Operator in SQL
The IN operator in SQL is used when you want to compare a column with multiple values. Similar to the OR condition in SQL.
Example of the IN Operator in SQL
If you want to find the names of students enrolled in either mathematics or science, the IN statement in SQL is written as follows:
SELECT first_name, last_name, subject
FROM student_details
WHERE subject IN ('Maths', 'Science');
You can add various different subjects to the list such as ('maths','science','history').
Note on the IN Statement in SQL
Data used for comparison is case-sensitive.
The IS NULL Operator in SQL
The value of a column is NULL if it doesn't exist. The IS NULL operator in SQL is used to display all rows containing columns with no value.
Example of the IS NULL Operator in SQL
If you want to find the names of students who have not participated in any games, the IS NULL statement in SQL is written as follows:
SELECT first_name, last_name
FROM student_details
WHERE games IS NULL
If all students participate in games, there will be no output returned. Otherwise, it will return the names of students who do not participate in games.
Here Mytour introduces you to some comparison operators, including BETWEEN...AND, IS NULL, LIKE, IN in SQL. To further understand clauses and commands such as RENAME, UPDATE, CREATE... or clauses like WHERE,... in SQL, readers can refer to some articles on Mytour such as installing SQL Server on Ubuntu to set up SQL Server environment on your computer.
