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

Hello everyone, welcome back to CybercityHelp. Since AND, OR and NOT operators are often used while defining multiple conditions in MySQL queries, they play a very important role in controlling how the queries return results.

So in our today’s article, we will be discussing how we can use these AND, OR and NOT operators in MySQL, along with different case examples to help you understand how these operators define conditions and alter MySQL queries. So let’s get started.

However, before continuing, we recommend new users to read our previous article because we have used some examples from that article. Here is the link to the previous article: MySQL Concepts: How to Retrieve Data Using SELECT and WHERE Clauses in MySQL.

What is AND Operator in MySQL?

The AND operator is nothing but the logical operator which we use to combine two or more conditions in an SQL query. We use this AND operator to ensure that all the specified conditions must be true for a record to be included in the result. And we follow some specific syntax to write the query.

Here is the syntax of AND operator:

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

How to use AND operator in MySQL Queries?

So, now let’s understand with the same query example. Here is the same query we had used in our previous article:

SELECT customerName
FROM customers.classicmodels WHERE creditLimit > 100000;

# Case 1 Example:

Let’s say, we have the same customer table, So if we run our same query, so we will get all the data of those customers whose credit limit is greater than one lakh. Okay, what if we also want to retrieve the data of only those customers whose credit limit is greater than one lakh and they are from the USA?

So, suppose if we write the query once again, so we write the query like this:

SELECT * FROM customers WHERE creditLimit > 100000 AND country = 'USA';

For example:

CybercityHelp

Then the AND operator over here denotes that both the conditions you have provided should be true. So, AND is used whenever you want all the conditions that you’re providing to be true.

So, with WHEREclause, we are writing multiple conditions, and here AND is used so that if we write country = 'USA' and run it again, we will only obtain the data of those customers whose country is USA and whose credit limit is greater than one lakh.

This is how you can use the AND operator. So, the AND operator over here denotes that both of these situations are mandatory.

# Case 2 Example:

Now, let’s suppose, we use the WHERE clause like this:

WHERE employee_number = 1165;

So, suppose there is this particular employee who is dealing with these people. As the sales representative employee number should be equal to 1165, and as it is an integer, we will be writing 1165 directly like this:

SELECT * FROM customers WHERE employee_number = 1165 AND country = 'USA';

For example:

CybercityHelp

If we run this query, so we will get, the data of those people whose customer representative employee number is 1165, whose credit limit is greater than one lakh, and who are from the USA.

So, all three conditions are true over here, and to make all the three conditions true, we use the AND operator between these conditions. This is how the AND operator is used.

# Case 3 Example:

If we slightly modify this query, it will look like this:

SELECT * FROM customers WHERE creditLimit > 100000 AND country = 'USA' AND sales_rep_employee_number = 1165;

For example:

CybercityHelp

So here, we have slightly modified the query in the above format to retrieve the data of sales representative by the employer number using AND operator effectively. You can also see, how we are able to alter the query and retrieving the data accurately without writing the queries multiple times. So by using AND operator, you can also try in the same manner with your existing database to retrieve the data.

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:

CybercityHelp

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:

CybercityHelp

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:

CybercityHelp

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:

CybercityHelp

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!”

Post Views: 77,521