Comparison operator LIKE, IN in SQL


Previous articles Taimienphi.vn introduced you to the common commands and clauses in SQL. Today’s article Taimienphi.vn will introduce you to the LIKE, IN comparison operators in SQL


Several comparative keywords are used in SQL to enhance the search capabilities of an SQL query, including “BETWEENAND“,”ISNULL“,”LIKE“,”PRINT”. This article Taimienphi.vn will introduce you to the comparison operators BETWEENAND, IS NULL, LIKE, PRINT in SQL.

Table of comparison operators including BETWEEN … AND, IS NULL, LIKE, IN in SQL:

like in sql 2

The LIKE operator in SQL

The LIKE operator in SQL is used to list all rows in a table whose values ​​match the specified pattern. The LIKE operator in SQL 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% character.

Example of 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 like this:

SELECT first_name, last_name

FROM student_details

WHERE first_name LIKE ‘S%’;

The output should look like the following:

like in sql 3

The above SELECT statement searches all rows in the name column with names that begin with the letter S and the rest are any characters.

Alternatively, you can use the underscore character (_) with the LIKE operator in SQL. In the search string, the underscores indicate a single character.

Example 2

To display all names starting with the letter “a”, the LIKE command in SQL is written as follows:

SELECT first_name, last_name

FROM student_details

WHERE first_name LIKE ‘_a%’;

The output should look like the following:

like in sql 4

Some notes with the LIKE operator in SQL

An underscore acts as a placeholder for a character. So you can use multiple underscores.

For example, “__i%” has two underscores on 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.

Example operator BETWEEN … AND in SQL

To find the names of students between the ages of 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 should look like the following:

like in sql 5

The IN operator is 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.

IN operator example in SQL

If you want to find the names of students who register for math or science, the IN command in SQL is written like this:

SELECT first_name, last_name, subject

FROM student_details

WHERE subject IN (‘Maths’, ‘Science’);

You can add many different subjects in the list like (‘maths’, ‘science’, ‘history’).

Note in the IN command in SQL

Data are used to compare case-sensitive.

The IS NULL operator in SQL

The value of the column is NULL if it does not exist. The IS NULL operator in SQL is used to display all rows that contain no columns.

For example the IS NULL operator in SQL

If you want to find the names of students not participating in any game, the IS NULL command in SQL is written as follows:

SELECT first_name, last_name

FROM student_details

WHERE games IS NULL

If all students participated in the game, no output will be returned. Otherwise it will return the names of the students not participating in the game.

https://thuthuat.taimienphi.vn/like-in-trong-sql-33278n.aspx
So above Taimienphi.vn has just introduced you to a number of comparison operators, including BETWEEN … AND, IS NULL, LIKE, IN in SQL. To learn more about clauses and commands like RENAME, UPDATE, CREATE … or clauses like WHERE, … in SQL, readers can refer to some articles on Taimienphi.vn such as install SQL Server on Ubuntu to set up SQL Server environment on your computer.

.

Add a Comment

Your email address will not be published. Required fields are marked *