MySQL Part 8: Logical Operators

Introduction

In this article, we discuss some simple logical operators in MySQL.  In the last article, we discussed the standard MySQL date functions.  Click the link below to check out that article.

MySQL Part 7: Dates

To begin your MySQL session in the goormIDE, please be sure to type this code into the terminal.

mysql-ctl cli

Please ensure that the bike_shop.sql file is selected and available.  If not, double-click it on the left-hand side.  This is where we will type most of our code. If there is information saved in there, go ahead and clear out that information.

WHERE Function

One of the most highly used logical operators in MySQL is the WHERE function.  In the first WHERE clause, we are filtering solely on customer_id equal (=) to 2.  In the second query, we are filtering on all customer_ids not equal (!=) to 2.  See the code and the different outputs below.

USE bike_shop_app;
 
SELECT * FROM orderlines WHERE customer_id = 2;
 
SELECT * FROM orderlines WHERE customer_id != 2;

You can combine logical operators to filter your results to get a more precise query result.  In the example below, we want to filter on all the customer_ids not equal to 2 with a quantity greater than 2.  For AND logical operations, you need to use the "&&" operators.  See the code and results below.

USE bike_shop_app;
 
SELECT * FROM orderlines 
    WHERE 
    customer_id != 2
    && quantity >= 2;

The query below will list any customer_id equal to 2 or any customer_id with a quantity of 2 or more.

USE bike_shop_app;
 
SELECT * FROM orderlines 
    WHERE 
    customer_id =  2
    || quantity >= 2;

LIKE Function

The LIKE function allows you to retrieve and filter rows based on a string.  For example, we would use the following statement if we wanted to filter on the bike_shop_name that contains "City" anywhere in the text.

USE bike_shop_app;
 
SELECT 
    bike_shop_id,
    bike_shop_name
    FROM customers
    
    WHERE bike_shop_name LIKE ('%City%');

You can also use it to search for text that begins or ends with a particular character or combination of characters.  For example, if you want to filter on any bike_shop_name that starts with a "P", we use the following query.

USE bike_shop_app;
 
SELECT 
    bike_shop_id,
    bike_shop_name
    FROM customers
    
    WHERE bike_shop_name LIKE ('P%');

If you want to filter on a row that lists and bike_shop_name that ends in "Equipment", we would use the following query.

USE bike_shop_app;
 
SELECT 
    bike_shop_id,
    bike_shop_name
    FROM customers
    
    WHERE bike_shop_name LIKE ('%Equipment');

IN Function

The IN function is excellent for filtering on more than one criteria.  For example, if we want to filter on two different bike_shop_names, the following query would get us the answer.

USE bike_shop_app;
 
SELECT 
    bike_shop_id,
    bike_shop_name
    FROM customers
    
    WHERE bike_shop_name IN ('Tampa 29ers', 'New York Cycles');

Next Steps

There are many different combinations and advanced ways to use the functions in conjunction with one another, but we have covered the main types of logical operators utilized in MySQL. In the next article, we will start to combine the data sets using joins.

MySQL Part 9: Joins

For a complete list of the entire set of MySQL-related articles, please see the links below.

MySQL Part 1: goormIDE
MySQL Part 2: Create Database
MySQL Part 3: Create Tables
MySQL Part 4: String Functions
MySQL Part 5: Refining Select Statements
MySQL Part 6: Aggregations
MySQL Part 7: Dates
MySQL Part 8: Logical Operations
MySQL Part 9: Joins