Logical Operators in MySQL Used For Data Filtering

In this post, you’ll learn three main logical operators used to filter data in MySQL database. The three logical operators we will look into are: AND, OR and NOT operators.

Let’s create a table called books_2 we will use as our working table. READ HOW TO CREATE A SQL TABLE

CREATE TABLE books_2 
	(
		book_id INT NOT NULL AUTO_INCREMENT,
		title VARCHAR(100),
		Author_Name VARCHAR(100),
		pages INT,
		PRIMARY KEY(book_id)
	);

INSERT INTO books_2 (title, Author_Name, pages)
VALUES
('The Namesake', 'Ediko', 32),
('48 Laws of Power', 'Robert Grenne', 400),
('100 Ways to Make it Big', 'Edward', 125),
('Back-end Engineering', 'Imtiaz', 345);

AND

AND logical operator in MySQL is used to combine multiple conditions in a WHERE clause. The conditions returns TRUE only if all the conditions separated by AND are true.

From the books_2 table above, let’s say i want to retrieve a row where book title is ‘The Namesake’ and book pages is 32, i will write the following code using the AND logical operator.

SELECT * FROM books_2 
WHERE title = 'The Namesake' AND pages = 32;

The above code will return the first row of data or record

OR

OR logical operator in MySQL is used to combine multiple conditions in a WHERE clause. The conditions returns TRUE if at least one of the conditions separated by OR is true.

From the books_2 table above, let’s say i want to retrieve a row where author name is ‘Imtiaz’ and book pages is 100, i will write the following code using the OR logical operator.

SELECT * FROM books_2 
WHERE Author_Name = 'Imtiaz' OR pages = 100;

The above query will return true and will return the last row because one of the condition Author_Name is true.

NOT

NOT logical operator is used to negate a condition. It returns TRUE if the condition is FALSE, and returns FALSE if the condition is TRUE.

From the books_2 table above, let’s say i want to retrieve a row where author name is NOT ‘Imtiaz’, i will write the following code using the OR logical operator.

SELECT * FROM books_2 
WHERE NOT Author_Name = 'Imtiaz';

The above code return the first three row of data and ignore the last row because the author name of the last row is ‘Imtiaz’