CODEWITHSUNDEEP
mysql
MrT
MrT

Reputation: 25

Why is this very simple MySQL query returning every row?

I'm trying to run a very very simple MySQL query that will fetch any rows where field pab_sku (of data type varchar(255)) of table product_attributes_basic is equal to a relatively short list of values. For some reason, this query is returning every row in the table.

(Aside: I am using MySQL Workbench, which has worked well for much more complex queries.)

SELECT *
FROM product_attributes_basic
WHERE pab_sku = 'abc' OR 'def';

The above returns every row in the entire table (several dozen thousand).

When I query for either string individually, no records are returned. (Which is correct; these are nonsense values.)

I've tried removing the apostrophes, but this has no effect; all rows return.

This is so simple that I'm at a loss as to a next step. Why on earth is this query returning every row?

Upvotes: 1

Views: 106

Answers (6)

Brian Minton
Brian Minton

Reputation: 3777

SELECT *
FROM product_attributes_basic
WHERE pab_sku = 'abc' OR pab_sku = 'def';

Upvotes: 0

Человек и PHP
Человек и PHP

Reputation: 99

Because:

SELECT *
FROM product_attributes_basic
WHERE pab_sku = 'abc' OR 'def';

The OR in this case is not looking at the row values at all, basically the syntax is valid but it's not doing what you think it's doing.

change to:

SELECT *
FROM product_attributes_basic
WHERE pab_sku = 'abc' OR pab_sku = 'def';

Upvotes: 1

SuperBiasedMan
SuperBiasedMan

Reputation: 9969

The problem is it's not splitting as you expect.

WHERE pab_sku = 'abc' OR 'def';

Is not the same as 

WHERE pab_sku = 'abc' OR pab_sku = 'def';

Your code, written in a wordy way actually means

WHERE pab_sku = 'abc' OR WHERE 'def'

WHERE 'def' then just gets evaluated as always True.

So you need to tell it explicitly that you're checking that value against pab_sku in both cases. With one of the following as others have suggested:

WHERE (pab_sku = 'abc' OR pab_sku = 'def');
WHERE pab_sku IN ('abc', 'def');

Upvotes: 5

Skizo-ozᴉʞS ツ
Skizo-ozᴉʞS ツ

Reputation: 20626

You have to do this query : 

SELECT *
FROM product_attributes_basic
WHERE pab_sku = 'abc' OR pab_sku = 'def';

Upvotes: 0

teoreda
teoreda

Reputation: 2570

You should modify your query like this:

SELECT *
FROM product_attributes_basic
WHERE (pab_sku = 'abc' OR pab_sku = 'def');

This because you have to apply your logical function OR not only to the strings but applied to your column.

Upvotes: 0

pavel
pavel

Reputation: 27082

You need to write column name again

SELECT *
FROM product_attributes_basic
WHERE pab_sku = 'abc' OR pab_sku = 'def'

Or use IN:

SELECT *
FROM product_attributes_basic
WHERE pab_sku IN ('abc', 'def')

Upvotes: 2

Related Questions