I was working with MySQL Queries. While working I am having problem with the queries containing IN operator. When IN is used with integer values it works properly. But when IN is used with floating-point values proper output is not coming. If WHERE clause is used to check two values with IN operator, in case of values with datatype float it only checks and returns the value which appears first in the table from the given values in query for few cases. Why this occurs and how to solve this?
Let there is a table customer with attribute customer_id int(4)
| customer_id |
|---|
| 3001 |
| 3003 |
| 3007 |
| 3008 |
| 3009 |
| 3012 |
Lets say there is query:
select customer_id from customer where customer_id in (3007,3008,3009);
Here it works fine and outputs the records containing these values under the specified attribute
But when there are values of float datatype a problem occurs
There is a table orders with attribute purch_amt float(6,2)
| purch_amt |
|---|
| 110.5 |
| 948.5 |
| 1983.43 |
If the query is:
select purch_amt from orders where purch_amt in(948.5,110.5);
It returns the records from the table for this case.
If the query is:
select purch_amt from orders where purch_amt in(948.5,1983.43);
It only returns the record containing the value which is present earlier in the table for this case.
Why is this occuring and how can we solve this?