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’