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 Postgres.
For our setup, we will use docker compose to create a Postgres database and to connect phpmyadmin. Start by copying the following into a docker compose file called
version: '3' services: db: image: 'postgres:latest' ports: - 5432:5432 environment: POSTGRES_USER: username POSTGRES_PASSWORD: password POSTGRES_DB: default_database volumes: - psqldata:/var/lib/postgresql phpmyadmin: image: phpmyadmin/phpmyadmin links: - db environment: PMA_HOST: db PMA_PORT: 3306 PMA_ARBITRARY: 1 restart: always ports: - 8081:80 volumes: psqldata:
We can run this file, we can use
docker-compose up. One this is done, open up phpmyadmin by going to http://localhost:8081.
You can then login by leaving the host empty and using the following credentials.
POSTGRES_USER: username POSTGRES_PASSWORD: password
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.
Next, let's create an
CREATE TABLE actor ( actor_id smallint, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (actor_id) ) ;
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 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.
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:
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
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%';
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
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';