Conditions in MySQL

Conditions in MySQL with Examples

In this article, I am going to discuss Conditions in MySQL with Examples i.e. we are going to understand the TRUE and FALSE Conditions in MySQL. Please read our previous article where we discussed Clauses in MySQL with Examples.

What are the Conditions?

In MySQL, a condition or an expression is made up of keywords, identifiers, and constants and it compares given values with the data rows values in a table. If the condition is matched with the data row it’s called a true condition otherwise a false condition. The syntax is given in the below image.

What are the Conditions?

Examples to Understand Conditions in MySQL:

We are going to use the following Employee table to understand Conditions in MySQL with Examples.

Examples to Understand Conditions in MySQL

Please use the following SQL Script to create the company database and employee table with the required records.

CREATE DATABASE company;
USE company;

CREATE TABLE employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);

INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1011, 'Pramod Panda', 'IT', 45000, 'Male', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1012, 'Preety Tiwary', 'HR', 55000, 'Female', 28, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1013, 'Santosh Dube', 'IT', 52000, 'Male', 28, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1014, 'Sara Talour', 'HR', 85000, 'Female', 26, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1015, 'Pamela Kar', 'Finance', 70000, 'Female', 26, 'London');
True Conditions in MySQL

A typical WHERE condition looks like, SELECT * FROM employee WHERE id = 1006; In this query, first the FROM clause will create an intermediate result set that contains all the data rows from the employee table. Then the system will filter the records using the WHERE clause with a condition id = 1006. It compares each row’s Id column value to the constant value 1006. System returns the data rows where the conditions are true. Everything happens all at once. The WHERE condition can be TRUE for one or more than one data row.

Let’s learn about the TRUE condition with some practical examples. Now type, SELECT * FROM employee; And it will display all the records from the employee table as shown in the below image.

True Conditions in MySQL

Now use SQL statement, SELECT * FROM employee WHERE id =1006; And we get one record where the Id column has the value 1006 as shown in the below image.

SELECT * FROM employee WHERE id =1006;

That is the WHERE condition provided in the SQL statement is true for this only one data row. Similarly, If we execute, SELECT * FROM employee WHERE age = 28; Then we will get the following data rows from the employee table.

SELECT * FROM employee WHERE age = 28;

And you can see in the above image, this time we get 6 rows where the given condition age=28 is true. All the data records have an age column value of 28. Now let’s add a condition, SELECT * FROM employee WHERE salary = 50000; Then we will get the following result set.

Conditions in MySQL with Examples

And we get all the data rows where the condition salary = 50000 is true. That is all the data records have the salary column value 50000.

FALSE Conditions in MySQL

As we already discussed, if a condition is not true it means the condition is FALSE. We can use false conditions to filter out the data that we don’t need. A simple example of a WHERE clause with a false condition is, SELECT * FROM employee WHERE NOT id = 1006; Here, you can see we get all the data rows except id=1006 as shown in the below image.

FALSE Conditions in MySQL

The NOT keyword followed by the condition inverts the truthfulness of the condition. We can also write, SELECT * FROM employee WHERE id <> 1006; Here “less than greater than” is a special symbol that represents “not equal to”.

Let’s learn about the FALSE condition with some practical examples. Let us execute a simple statement to show all the records from the employee table. SELECT * FROM employee; In the returned result set we have all the data rows that are stored in our employee table as shown in the below image.

SELECT * FROM employee;

Let’s get the result set again but this time we will exclude the data row where the Department is not equal to IT. The SQL statement is, SELECT * FROM employee WHERE NOT department = ‘IT’; you will get the following result set.

SELECT * FROM employee WHERE NOT department = 'IT';

When we execute this statement, the system will look for the “department” column and check if each data row “department” value is NOT IT. When it finds the rows, where is the department value is IT, the condition NOT department = ‘IT’ becomes false, And the system won’t include that data row in the result set.

Let’s execute another SQL statement using the second method, and this time SELECT * FROM employee WHERE city <> ‘London’; In this SQL statement, we are using a special symbol “less than greater than’ which represents ‘not equal to”. And we want to get a result set where the city column value is not equal to ‘London’.

Conditions in MySQL with Examples

And, you can see in the result set, the system excluded the data rows where the city value is ‘London’.

In the next article, I am going to discuss Comparison Operators in MySQL with Examples. Here, in this article, I try to explain Conditions in MySQL with Examples and I hope you enjoy these Conditions in MySQL article.

Leave a Reply

Your email address will not be published. Required fields are marked *