MariaDB SQL OR operator issue

Help Desk
1

I am hoping someone can help me with a SQL question. I don’t think its specific to MariaDB but that is the database I have a question on.

My question is on the SELECT statement in particular the OR operator within SELECT.

When I use the OR operator trying to do a query. I want to see the results from a table named titles (within employees database) that shows the employee number that is greater than 200,000 (emp_no) OR does NOT contain ‘Engineer’ in the title(title). If I do the statement with an AND operator it works. When I change it to an OR operator neither column returns the results from the WHERE statement.

##This works.
SELECT * FROM titles WHERE emp_no > ‘200000’ AND title !=‘engineer’;

This returns results but ignores both the > ‘200000’ and the !=‘engineer’.

SELECT * FROM titles WHERE emp_no > ‘200000’ OR title !=‘engineer’;

I have tried with and without (') around 200000 and case does not matter.

Hopefully this is OK to ask here maybe someone can point me to a place to find the answer (I am watching more videos on this after a couple days of reading). Thanks in advance.

2

I’m no expert by any means, and my suggestion is probably worth less than you’re paying for it…

I remember something weird about using a != in conjunction with a = in an OR statement.

First I’d make sure that each half of your query is returning results as you’d expect (if you haven’t done that already)

you shouldn’t need them in theory, but you might try adding parentheses around the statements

3

Yes I did make sure each half works by itself which they do.
Adding the () around the != as you suggested gets me half way there. It now honors the != but is still ignoring >200000. So that is progress at least. I’ll keep at it and see if I can get the other half of the statement to work. Thank you for the help!

4

Welp, I think I was not understanding the OR clause properly as it will take either statement that evaluates as true. Anyhow I got the results I was looking for by using the LIKE operator.
Solution was this in the end. Thank you.

select * from titles where emp_no > 200000 OR title NOT LIKE ‘%Engineer%’;

1 Like
5

I’d try replacing != With <>

6

That’s what it was! I did some messing around mariaDb for a project last year, and ran into some similar issues. The OR statement isn’t quite what you’d expect it to be.

Glad you got it sorted out!