How to use IN() Operator not working it’s.
Those table are example and look the same as the real database I have.I don’t have the permitting to add tables or change
Those are the tables:
students +------+------+ | id | name | +------+------+ | 1 | ali | | 2 | man | | 3 | sos | +------+------+ Classes +------+---------+ | c_id | students| +------+---------+ | 1 | 1,2,3,4 | | 2 | 88,33,55| | 3 | 45,23,72| +------+---------+
When I use this query it return me only the student with id =1 because “id IN (students)” return 1 when the first value are equal.
select name,c_id from students,classes where id IN (students);
when I get the list out on PHP than add it. it work fine.But, this solution need a loop and cost many queries.
select name,c_id from students,classes where id IN (1,2,3,4);
FIND_IN_SET()
the same happened, it’s only return 1 but if the value on other position it return 0.
Advertisement
Answer
The IN operator works just fine, where it’s applicable for what it does.
First, consider restructuring your data to be normalized, and avoid storing values as comma separated lists.
Second, if you absolutely have to deal with columns containing comma separated lists of values, MySQL provides the FIND_IN_SET() function.
FOLLOWUP
Ditch the old-school comma syntax for the join operation, and use the JOIN keyword instead. And relocate the join predicates from the WHERE clause to the ON clause. Fully qualify column references, eg.
SELECT s.name
, c.c_id
FROM students s
JOIN classes c
ON FIND_IN_SET(s.student_id,c.students)
ORDER BY s.name, c.c_id
To reiterate, storing a “comma separated list” in a column is an anti-pattern; it flies against relational theory and normalization, and disregards the best practices around relational databases. O
One might argue for improved performance, but this pattern doesn’t improve performance; rather it adds unnecessary complexity in query and DML operations.