0

I want to do a select request in mysql, but I don't know how to filter it. My filter clause is on the column A.present and contain the value 0,1, null. I want to exclude only the 1 value.

My request looks like

Select *
from A,
left join B on A.b_id = B.id
where A.present <> 1

But this request doesn't return the null value. How can I get it? THank you

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
mattt
  • 1
  • 1
  • 1
    Does this answer your question? [MySQL SELECT WHERE EQUALS syntax](https://stackoverflow.com/questions/11036422/mysql-select-where-equals-syntax) Edit: I may have misunderstood. I think you're looking for `WHERE A.present = 0 OR A.present IS NULL` or `A.present != 1 OR A.present IS NULL`. https://stackoverflow.com/questions/16186674/mysql-syntax-not-evaluating-not-equal-to-in-presence-of-null may be a better reference which references `<=>` (https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html) as another option – WOUNDEDStevenJones Jan 07 '21 at 16:14

1 Answers1

0

In mysq you could try using ifnull in wehere clause

Select *
from A 
left join B on A.b_id = B.id
where ifnull(A.present,0) <> 1

or avoiding function you could use

Select *
from A 
left join B on A.b_id = B.id
where A.present is not null and A.present <> 1
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • though would `ifnull()` utilize an index on `A.present` as efficiently as not using a function in a where clause? I would suggest you vote to close this question instead of answering it, considering it's a duplicate of other similar questions on the site (https://stackoverflow.com/questions/16186674/mysql-syntax-not-evaluating-not-equal-to-in-presence-of-null). – WOUNDEDStevenJones Jan 07 '21 at 16:21
  • 1
    @WOUNDEDStevenJones ..answer updated for a solution without function too – ScaisEdge Jan 07 '21 at 16:23