Use MySQL's IN function to filter records with specific values

Use MySQL's IN function to filter records with specific values

In MySQL, the IN function is a very powerful tool that can be used to filter out records that meet specific value conditions. The IN function can combine multiple parameter values ​​and be used in the WHERE clause of a SELECT statement to select records from the database that contain those specific values.

Suppose we have a table named "students" that stores basic information about students. The fields included in this table are: student ID, name, age, gender, class, etc. Now we want to filter out information about students who belong to certain classes. At this time, you can use the IN function to easily complete this task.

First, let us create a table named "students" and insert some sample data:

CREATE TABLE students (
  stu_id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  gender VARCHAR(10),
  class_id INT
);

INSERT INTO students (stu_id, name, age, gender, class_id)
VALUES 
  (1, '张三', 18, '男', 1),
  (2, '李四', 19, '女', 2),
  (3, '王五', 20, '男', 1),
  (4, '赵六', 21, '女', 3),
  (5, '钱七', 19, '男', 2),
  (6, '孙八', 20, '女', 2);

Now, we can use the IN function to filter out the students who belong to class 1 and class 2 Student information:

SELECT * FROM students
WHERE class_id IN (1, 2);

In the above SQL statement, the parameter of the IN function is a list containing specific class numbers, that is, "1, 2". This means that we want to select information about students belonging to Class 1 and Class 2. After executing the above SQL statement, we will get the following results:

+--------+---------+-----+--------+----------+
| stu_id |  name   | age | gender | class_id |
+--------+---------+-----+--------+----------+
|      1 | 张三    |  18 | 男      |        1 |
|      2 | 李四    |  19 | 女      |        2 |
|      3 | 王五    |  20 | 男      |        1 |
|      5 | 钱七    |  19 | 男      |        2 |
|      6 | 孙八    |  20 | 女      |        2 |
+--------+---------+-----+--------+----------+

As you can see, only the student information belonging to Class 1 and Class 2 has been filtered out.

In addition to using specific values ​​as parameters, the IN function can also accept subqueries as parameters. For example, we can use a subquery to query student information in a specific class:

SELECT * FROM students
WHERE class_id IN (SELECT class_id FROM classes WHERE class_name = '一班');

In the above SQL statement, the subquery (SELECT class_id FROM classes WHERE class_name = 'Class 1') will return The class number is the class name "Class 1", and then the class number is passed to the IN function as a parameter to filter out the student information belonging to "Class 1".

Through the flexible application of the IN function, we can easily filter out records in the database that meet specific value conditions. Whether by specifying parameters directly or obtaining parameters through a subquery, the IN function can help us get the information we want. When we need to filter specific values ​​in the database, we can try to use the IN function to simplify query operations and improve efficiency.

To summarize, this article introduces how to use MySQL's IN function to filter records with specific values. Through the demonstration of the sample code, we understand the basic usage of the IN function, which can combine multiple parameter values ​​to filter specific values. The application of IN function can not only simplify query operations, but also improve the efficiency of database operations. When you use MySQL for data query, you can consider using the IN function to filter specific values.

The above is the detailed content of Use MySQL's IN function to filter records with specific values. For more information, please follow other related articles on the PHP Chinese website!