Working with MySql Where

Intro

The WHERE cause allows you to filter results returned by the SELECT statement. For example, we can filter a list of actors by their first name. In this article, we will learn how to use the WHERE in SQL.

Getting Setup

We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called docker-compose.yml and add the following.

version: '3'
 
services:
  db:
    image: mysql:latest
    container_name: db
    environment:
      MYSQL_ROOT_PASSWORD: root_pass
      MYSQL_DATABASE: app_db
      MYSQL_USER: db_user
      MYSQL_PASSWORD: db_user_pass
    ports:
      - "6033:3306"
    volumes:
      - dbdata:/var/lib/mysql
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    container_name: pma
    links:
      - db
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
    restart: always
    ports:
      - 8081:80

volumes:
  dbdata:

Next, run docker-compose up.

Now, navigate to http://localhost:8081/ to access phpMyAdmin. Then log in with the username root and pass root_pass.

Click the SQL tab and you are ready to go.

Creating a DB

In this article, we will need some data to work with. If you don't understand these commands, don't worry, we will cover them in later articles.

We will be using the sample db provided here: https://dev.mysql.com/doc/sakila/en/. However, we will only enter what we need rather than import the whole db.

With the SQL tab open (or your own sql cli going), let's first create our DB and select it.

create DATABASE if not EXISTS sakila;

USE sakila;

Next, let's create an actor table.

CREATE TABLE actor (
  actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (actor_id),
  KEY idx_actor_last_name (last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

And finally, let's enter a few rows.

INSERT INTO actor VALUES (1,'PENELOPE','GUINESS','2006-02-15 04:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 04:34:33'),
(3,'ED','CHASE','2006-02-15 04:34:33'),
(4,'JENNIFER','DAVIS','2006-02-15 04:34:33'),
(5,'JOHNNY','LOLLOBRIGIDA','2006-02-15 04:34:33'),
(6,'BETTE','NICHOLSON','2006-02-15 04:34:33'),
(7,'GRACE','MOSTEL','2006-02-15 04:34:33'),
(8,'MATTHEW','JOHANSSON','2006-02-15 04:34:33')

The Basic SQL

The basic sql structure as the following pattern.

SELECT * FROM actor WHERE first_name = 

Here we filter our actors by their first name. If there are multiple matches, SQL will return multiple rows.

Using Operators

When using the WHERE clause, we don't have to only use the = operator. We have many different operators to use. A list of common operators are:

• Less Than (<)
• Less Than or Equal To (<=)
• Greater Than (>)
• Greater Than or Equal (>=)
• Not Equal (<> or !=)

Here are a few examples.

SELECT * FROM actor WHERE id < 5 

SELECT * FROM actor WHERE id != 7 

We also have a few string operators we can use. Here is are examples using LIKE and NOTLIKE. Notice that we wrap the string we want to compare in %.

SELECT * FROM actor WHERE id != 7 

SELECT title FROM film WHERE title LIKE '%family%';

SELECT title FROM film WHERE title NOT LIKE '%family%';

Logical Operators

Now that we have know the basic usage of the where clause, we can start to combine queries using logical operators. We have AND, OR, NOT, and XOR. Let's start with an example of using AND.

For example, if we would like to filter a user by first name and last name we can use the following.

select * from actor where first_name = 'PENELOPE' and last_name = 'GUINESS';

In a similar way, we can use or instead of and to get actors that match first name or the last name. `

select * from actor where first_name = 'PENELOPE' or last_name = 'GUINESS';

We can combine logical operators, but in general we should use paranthesis to ensure that operations are evaluated in the correct order. Here we add parens around the or so that sql checks the or first.

select * from actor 
	where (first_name = 'PENELOPE' or first_name = 'SEAN')
    and last_name = 'GUINESS';