Logical operators in MySQL are used to manipulate or combine Boolean conditions in SQL select statement (queries). Logical operators are essential for filtering data in WHERE clauses, joining conditions, and controlling query logic.
Let’s create a table called employees we will use as our working table
CREATE TABLE employees (
EmployeeID int NOT NULL,
EmployeeName varchar(150) DEFAULT NULL,
Department varchar(150) DEFAULT NULL,
ContactNo varchar(12) DEFAULT NULL,
Email varchar(100) DEFAULT NULL,
AnnualSalary int DEFAULT NULL,
PRIMARY KEY (EmployeeID)
);
INSERT INTO employees VALUES
(1,'Seamus Hogan', 'Recruitment', '351478025', 'Seamus@techsmansion.com',50000),
(2,'Thomas Eriksson', 'Legal', '351475058', 'Thomas.e@techsmansion.com',75000),
(3,'Simon Tolo', 'Marketing', '351930582','Simon.t@techsmansion.com',40000),
(4,'Francesca Soffia', 'Finance', '351258569','Francesca.s@techsmansion.com',45000),
(5,'Emily Sierra', 'Customer Service', '351083098','Emily@techsmansion.com',35000),
(6,'Maria Carter', 'Human Resources', '351022508','Maria.c@techsmansion.com',55000),
(7,'Rick Griffin', 'Marketing', '351478458','Rick.G@techsmansion.com',50000);IN
IN logical operator checks if a value in a query matches any value in a list. Let’s use the IN operator to check for employees in marketing, finance and legal department whose annual salary is less than 50000 from the table above.
SELECT * FROM employees
WHERE AnnualSalary < 50000 AND Department IN ('Marketing', 'Finance', 'Legal');It will return the result below
BETWEEN
Between logical operator checks if a value is within a range and the range is inclusive.
From the employees table, let’s query the employees whose annual salary is between 35000 and 50000 (note: 35000 and 50000 is inclusive)
SELECT * FROM employees
WHERE AnnualSalary BETWEEN 35000 AND 50000;It return the result below:
LIKE
Like logical operator checks if a value matches a pattern (using % for wildcards) in a query. The code below selects all Employees from the employees table whose name start with S and followed by single or multiple characters.
select * from employees
where EmployeeName LIKE 'S___%';It return the following result
