CODEWITHSUNDEEP
mysqlsql
user2487726
user2487726

Reputation:

MySQL Unexpected Result from "in (' ' or ' ')"

What I'm Using: The most recent MySQL on Ubuntu 12.

The Set Up: Suppose I have a table "EmployeePayment" with "Name" and "Hours" for each employee. Suppose I already have it populated with values.

The Question: When I use the command

select * from EmployeePayment where Name in ('');

I get the empty set, as I'd expect. But, when I use

select * from EmployeePayment where Name in ('' or '');

I get the entire table returned. Moreover, if I'm picky and put in the command

select Name, SUM(Hours) from EmployeePayment where Name in ('' or '');

then it only returns whatever is the top name from the table. What's happening with this "in" command?

Upvotes: 0

Views: 61

Answers (3)

paxdiablo
paxdiablo

Reputation: 881463

First off, you need to get rid of the or, the proper syntax for the in clause uses commas to separate the possibilities, such as:

sql> select name from people where status in ('intelligent', 'good looking')
pax
1 row returned

What your current variant is doing is applying the or operator to give you a one-element in-list. See here for more detail.

The reason why you're only getting one row for the aggregated query is because you have no group by clause, so you're grouping all rows. Most DBMS' would then complain about having a non-aggregated column that isn't part of the grouping, but MySQL is a bit fancy-free and footloose with the rules in that regard.

It's obviously grouping over the whole table (as it should) but applying some default aggregating function to the name (which it probably shouldn't, but does according to its documentation).

This MySQL extension is covered here but heed the warning: MySQL can choose any of the myriad possible values for these non-aggregated, non-group-by columns, so it's more useful when you know that all the rows in a given group share the same value for the column.

Upvotes: 5

bfavaretto
bfavaretto

Reputation: 71918

You're effectively doing this:

select * from EmployeePayment where Name in (0);

The OR expression evaluates to 0, and WHERE Name IN (0); returns all rows. You have to use the proper IN syntax as suggested in the other answers:

SELECT * FROM EmployeePayment WHERE Name IN ('foo', 'bar');

Upvotes: 1

d-_-b
d-_-b

Reputation: 23171

IN uses comma separated values, for example: WHERE Name IN ('tim','beth')

So try WHERE Name IN ('','');

But more importantly, why would you want to check where a value is empty or empty? Or was that just to get the question across?

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in

Upvotes: 0

Related Questions