Previous articles by TipsMake have introduced you to common commands and clauses in SQL. Todays article will continue by introducing you to the LIKE and IN comparison operators in SQL.
Several comparison keywords are used in SQL to enhance the searchability of an SQL query, including " BETWEEN . AND ", " IS NULL ", " LIKE ", and " IN". This article from TipsMake will introduce you to the BETWEEN . AND , IS NULL , LIKE , and IN comparison operators in SQL.
The list of comparison operators in SQL includes BETWEEN.AND, IS NULL, LIKE, and IN:
The LIKE operator in SQL
The LIKE operator in SQL is used to list all rows in a table whose values match a specified pattern. The LIKE operator is particularly useful if you want to search for rows that match a specified pattern, or if you don't know the entire value. In this case, use the % wildcard.
Example of the LIKE operator in SQL
Example 1
To select all students in a class whose names begin 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 will be in the following format:
The SELECT command searches for all rows in the name column that contain names starting with the letter S, with the rest being any other character.
Alternatively, you can use the underscore (_) wildcard with the LIKE operator in SQL. In a search string, underscores represent a single character.
Example 2
To display all names that begin 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 will be in the following format:
Some notes on the LIKE operator in SQL
An underscore acts as a placeholder for a character. Therefore, you can use multiple underscores.
For example, "__i%" has two underscores to the left, or "S__j%" has two underscores between the characters 'S' and 'i'.
The BETWEEN . AND operator in SQL
The BETWEEN . AND operator in SQL is used to compare data for a range of values.
For example, the BETWEEN . AND operator in SQL.
To find the names of students aged 10 to 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 will be in the following format:
The IN operator in SQL
The IN operator in SQL is used when you want to compare a column with multiple values. It's similar to the OR condition in SQL.
Example of the IN operator in SQL
If you want to find the names of students registered for math or science, the IN command in SQL is written as follows:
SELECT first_name, last_name, subject
FROM student_details
WHERE subject IN ('Maths', 'Science');
You can add many different subjects to the list such as ('maths', 'science', 'history').
Note regarding the IN statement in SQL
The data is used for case-sensitive comparison.
The IS NULL operator in SQL
A column's value is NULL if it doesn't exist. The IS NULL operator in SQL is used to display all rows containing columns with no values.
Example of the IS NULL operator in SQL
If you want to find the names of students who did not participate 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 the game, no output will be returned. Otherwise, it will return the names of the students who did not participate in the game.
In summary, TipsMake has introduced you to some comparison operators, including BETWEEN.AND, IS NULL, LIKE, and IN in SQL. To learn more about clauses and commands such as RENAME, UPDATE, CREATE, etc., or clauses like WHERE, etc., in SQL, you can refer to some articles on TipsMake, such as " Installing SQL Server on Ubuntu, " to set up a SQL Server environment on your computer.