Selecting where data `not exists` and selecting columns that are empty or `is null` - MySQL

Question

This is the query I have currently:

select * from outerb where not exists(
Select * from wms
where wms.barcode = outerb.barcode);

This query returns me values that I need.

So far so good...

Next I needed to select a specific column (in this case pcode) that have no values written in there. Here is what I have tried and worked absolutely fine for me:

select * from outerb where not exists(
Select * from wms
where wms.barcode = outerb.barcode) 
and pcode = "" or pcode is null;

But when I add another column to the query, it returns me empty column for pcode but for the new column, it returns me empty values and with filled values. Which is I am confused about. This is what I have tried:

select * from outerb where not exists(
Select * from wms
where wms.barcode = outerb.barcode) 
and pcode = "" or pcode is null
and
brand = "" or brand is null;

Pcode works absolutely fine but brand doesn't.. whats the problem?

But if I reverse the two, so brand comes first in the query and pcode comes 2nd, its pcode this time that shows up with values and brand without values.

Both columns are Varchar type

Caius Jard · Accepted Answer · 2019-05-11T04:51:07.193

Put some brackets in

select * 
from outerb 
where 
  not exists(
   select * 
   from wms
   where 
     wms.barcode = outerb.barcode
  ) 
  and (pcode = '' or pcode is null)
  and (brand = '' or brand is null);

Whenever you mix AND and OR, use brackets to make it clear to both the DB and the person who maintains this after you, which set of truths go together. Without them, MySQL will first evaluate any ANDs then evaluate any ORs. This means when You say:

a AND b OR c AND d OR e

MySQL will do, vs what you wanted:

(a AND b) OR (c AND d) OR e --MySQL does
a AND (b OR c) AND (d OR e) --you wanted

Very different set of truths, those two

Take a look at Mysql or/and precedence? for more info

Footnote from Tim and a general advice when working with MySQL- MySQL can be a bit like Visual Basic 6- in its default settings it allows you to be a bit more sloppy with your coding than most other database. Things like not having to provide an explicit GROUP BY on a query that uses aggregates; it will just invent a grouping for you. It also allows use of double quotes for strings when the SQL standard is single quotes. The SQL standard uses double quotes for quoting column names etc, so SELECT a as "my column name with spaces" - MySQL allows strings to be double quoted too, which isn't to spec. So why stick to spec? It makes your database skills a lot more portable - if you learn standard sql it works in more places than MySQL specific sql, means you can say with greater confidence in a job interview that your database cross skilling is good because you've used a range of databases via focusing your learning on the sql standard, rather than saying "I've only ever used MySQL but I'm sure I can learn ..". Of course you'll always run into vendor specific stuff, as the standard doesn't keep up with consumer demand like the implementations do. Side note; I believe Postgres is one of the better databases for strict adherence to SQL spec and hence makes for a good learning DB

for th same reasons (2+3)*5 is 25, but both 2+(3*5) and 2+3*5 equals 17. There are rules about which things are done first. Brackets are done first then divide/multiply, then add/subtract. MySQL is no different to basic maths and has rules about which thins are done first. It will do the ANDs first then the ORs, but you wanted the or first, then the and — Caius Jard, May 10 '19 at 10:36
Be careful using double quotes to represent string literals. In some databases, double quotes are used to represent identifiers. — Tim Biegeleisen, May 11 '19 at 00:34
True; I did just literally copy the op's query, and i normally change and note that poor you made. I'll fix it up now — Caius Jard, May 11 '19 at 04:37

score 1 · Answer 2 · answered May 10 '19 at 10:31

1

This is probably an order of operations problem with AND having higher precedence than OR. Try this version:

SELECT *
FROM outerb
WHERE NOT EXISTS (SELECT 1 FROM wms WHERE wms.barcode = outerb.barcode) AND
    (pcode = '' OR pcode IS NULL) AND
    (brand = '' OR brand IS NULL);

But, we could actually simplify to avoid this problem entirely, using COALESCE:

SELECT *
FROM outerb
WHERE NOT EXISTS (SELECT 1 FROM wms WHERE wms.barcode = outerb.barcode) AND
    COALESCE(pcode, '') = '' AND
    COALESCE(brand, '') = '';

answered May 10 '19 at 10:31

Tim Biegeleisen

502,043
27
286
360

why have you replaced the `*` with a `1`? and also how does `COALESCE` simplify this query? Just curious :) – May 10 '19 at 10:33
There might not be a difference between `SELECT *` and `SELECT 1`, but the idea behind an `EXISTS` clause is that it only checks for the existence of the record. What that record contains, how many columns, etc., should not matter, so we might be inclined to use `SELECT 1` just in case it makes things faster. Regarding `COALESCE`, it is just a shorter way to write your logic, IMO. – Tim Biegeleisen May 10 '19 at 10:34
It doesn't matter what the inner query in an exists returns, even exists(select null ...) works – Caius Jard May 10 '19 at 10:38
@CaiusJard But are you certain of that for every flavor of SQL? Maybe folks use `SELECT 1` just because it is short and easy to read :-) – Tim Biegeleisen May 10 '19 at 10:41
@TimBiegeleisen reasonably certain, aye - I always understood that it's the presence of a row that EXISTS is looking for, rather than caring about any particular value on that row (if it did care about null , then what to do with `EXISTS(SELECT * FROM table_with_one_column_and_one_row_that_is_null)` - we typically find that even illegal things like `EXISTS(SELECT 1/0 FROM table)` or `EXISTS(SELECT a FROM table GROUP BY b)` work out in most rdbms,which implies it doesn't even try to evaluate the output list once it knows there is/isnt a row ;) – Caius Jard May 10 '19 at 17:22
Be careful with COALESCE, or any functions, by the way- calling a function and passing in a column may prevent use of an index on that column in a situation where "col = value or col is null" would allow the index to be used. Performance testing in the real life situation is wise – Caius Jard May 10 '19 at 18:09

score 0 · Answer 3 · answered May 10 '19 at 10:30

0

You cannot mix OR and AND together without specifying what is grouped together. Use brackets to group them.

select * from outerb where not exists(
Select * from wms
where wms.barcode = outerb.barcode) 
and (pcode = "" or pcode is null)
and
(brand = "" or brand is null;)

answered May 10 '19 at 10:30

Muhammad Ali

668
1
9
24

1

Thanks Ali!, now it makes sense to me – May 10 '19 at 10:31
Awesome! Don't forget to mark a correct answer to question. – Muhammad Ali May 10 '19 at 10:35

score 0 · Answer 4 · answered May 10 '19 at 10:32

0

Add bracket to your query

select * from outerb where not exists(
Select * from wms
where wms.barcode = outerb.barcode) 
and 
(pcode = "" or pcode is null)
and
(brand = "" or brand is null);

answered May 10 '19 at 10:32

gauri

121
1
3
14

Selecting where data `not exists` and selecting columns that are empty or `is null` - MySQL

4 Answers4