MySQL Operators: How to use AND, OR, and NOT Operators in MySQL?

What is OR Operator in MySQL?

The OR operator is also a logical operator which we use to combine two or more conditions in an SQL query. We use OR operator to returns records if any one of the specified conditions is true using it’s OR operator syntax.

Here is the syntax of OR operator:

SELECT column_name(s)
FROM table_name
WHERE condition1 OR condition2;

How to use OR operator in MySQL Queries?

So now let’s understand how we use OR operator. So the OR operator is used when you want to give multiple conditions. For example, in job applications, it is often mentioned that your degree should be a bachelor’s degree like whether it is B.Tech, BCA, B.Ed, or B.Sc any bachelor’s degree is applicable.

Here “Any bachelor’s degree” means, if in this condition, we want to obtain the data of the customers who are either from Germany or from the USA, in that condition, we can use the OR operator. That means we want the data of both the countries basically. So, whenever the country is USA or whenever the country is Germany, we want to obtain that particular data.

# Case 1 Example: 

For example, you can write the query in this way with the help of OR operator:

SELECT * FROM customers WHERE (country = 'Germany' OR country = 'USA') and creditLimit > 100000;

For example:

Now many of you who are familiar with SQL might say that the IN operator could also be used over here, but right now we can’t explain at this point, that we answer in our upcoming articles because if we start here, this article will be so long, so now let’s continue.

So as of now, as we are discussing the OR operator, so we will be talking about OR operator only. As you can see in the given below image, in the country section, we only have the countries USA or Germany.

# Case 2 Example: 

Similarly, you can provide multiple conditions using the OR operator. You can also mix and match. For example, suppose we want to get the data of those people who are from Germany and their credit limit is greater than one lakh, and who are from the USA whose credit limit is greater than one lakh.

Let’s have a look on that. So we can again modify the query in this way:

WHERE (country = 'USA' AND creditlimit > 100000)

OR (country = 'Germany' AND creditlimit > 100000);

For example:

Note that you have to enclose it in brackets whether the country is USA or Germany but this one condition is mandatory. If we run this query, we will receive the data of both USA and Germany where the credit limit is greater than one lakh. This is how AND and OR operators work. And you can also use in the same manner as we did here in our case.

What is NOT operator in MySQL?

The NOT operator is also a logical operator which is used to reverse the result of a condition. For example, we use this operator to return TRUE if the condition is FALSE, and FALSE if the condition is TRUE. It’s basically used to negate conditions in a WHERE clause when we need to exclude certain results from the query using it’s syntax.

Here is the syntax of NOT operator:

SELECT column_name(s)
FROM table_name
WHERE NOT condition;

How to use NOT operator in MySQL Queries?

The NOT operator is very simple. Let’s the same example like If we want to obtain all the data except from the country USA and the country Germany, in that condition we can use the NOT operator. Let’s understand with some case examples.

# Case 1 Example: 

For example, if we modify the query again to apply NOT operator, so we can write the query in this way:

SELECT * FROM customers WHERE NOT country = 'USA';

For example:

So based on this above query, It will give us the data of all customers except the people data those who are from the USA. So this is how we can use NOT operator.

# Case 2 Example:

Similarly, if we write with the query again with bracket like this:

SELECT * FROM customers WHERE NOT (country = 'USA' OR country = 'Germany');

For example:

Now, we will be getting all the data except for the people who are from Germany or the USA. This is how it works. You need to put a NOT operator, but just make sure before you’re putting it, if you are using multiple conditions inside, close them inside the parentheses ().

Follow the method of BODMAS like how we been followed since our schooling. Like how bracket, then off then divide like this come first. So, put things inside the brackets so that they get solved first, and then later the other things will get solved in the order of BODMAS.

Alright, so these were the basics of AND, OR, and NOT operators in MySQL. We have explained them in the simplest language so that you can easily understand. But in case if you still have any doubts about any operator, feel free to ask your questions in the comment section.

If you want to learn more MySQL concepts, you can explore other articles from our header menu or category list. So that’s all for today’s article. In our upcoming articles, we will discuss more MySQL topics such as JOINS, ORDER BY, GROUP BY, and many more. Thank you so much for reading this article till the end!!

“So keep learning, keep growing!”