[MySQL] Day 14. Logical Operators (AND, OR, NOT, BETWEEN, IN)

We are going to learn about using the logical operators, AND, OR, and NOT. 

We can combine more than one conditions with those operators. 

 

Let's start from the previously built employees table. 

 

 

I want to add a job column into the table. 

ALTER TABLE employees
ADD COLUMN job VARCHAR(25) AFTER hourly_pay;

SELECT * FROM employees;

 

 

I will assign positions to each employee. 

UPDATE employees
SET job = "manager"
WHERE employee_id = 1; 

UPDATE employees
SET job = "cashier"
WHERE employee_id = 2; 

UPDATE employees
SET job = "cook"
WHERE employee_id = 3; 

-- ... and more

From the table, you can see that both Spongebob and Patrick are working as a cook. 

What if we want to find a cook who is specifically hired before January 5th? 

Then, we would want to use the AND logical operator! 

 

 

Using AND operator is easy. You can simply join two conditions with AND keyword. 

SELECT * FROM employees
WHERE hire_date < "2023-01-05" AND job = "cook";

 

 

Now, let's try to find every employee that is either a cook OR a cashier. 

SELECT * FROM employees
WHERE job = "cook" or job = "cashier";

 

 

As you may guessed already, NOT keyword simply reverses the condition. 

Let's try to choose every employee except manager and asst. manager.

SELECT * 
FROM employees
WHERE NOT job = "manager" AND NOT job = "asst. manager";

 

 

There is another logical operator that is used with AND, but used just for the readability. 

It's BETWEEN operator. Let's try choosing every employee hired in between Jan 4th - Jan 6th

SELECT * FROM employees
WHERE hire_date BETWEEN "2023-01-04" AND "2023-01-06";

 

 

Lastly, there is one more logical operator, IN. 

Let's check out an example query of choosing the employees with certain jobs. 

SELECT * FROM employees
WHERE job IN ("cook", "janitor", "asst. manager");

We got all employees with job in the list (cook, janitor, asst. manager)