Skip to main content

Introduction to the Where Clause in SQL

In this tutorial, you will be introduced to filtering rows in SQL using the where clause.
Mar 2019  · 7 min read

Filtering rows from a table with respect to a given condition or a set of conditions is a very common practice in data analysis. In SQL, you can do this using the where clause. In this tutorial, you will be using it for a number of different scenarios.

You will use PostgreSQL as the RDBMS (Relational Database Management System), but the syntaxes do not vary much when it comes to using other database systems. You will start off by restoring a PostgreSQL database with a relevant table to work upon, and then you will execute several filter queries incorporating the where clause.

Note: To follow along with the tutorial, you must be familiar with the basics of SQL and PostgreSQL. Following resources might be helpful if you are not familiar with them.

Restoring a PostgreSQL database

Almost every RDBMS allows us to take a backup copy of a particular database and restore it. For this tutorial, you will use this backup file into a PostgreSQL database. In PostgreSQL, database backups as exported as .backup files. The given DB backup has a table within itself which would be used throughout this tutorial. To restore the backup, you will need to first create a database in PostgreSQL. So, open up the pgAdmin tool and create a new PostgreSQL database. Give it a name of your choice.

After the database is created, right-click on it and select Restore. The following dialog-box should open up -

dialog-box screenshot

Keep the settings as shown in the above figure. You will have to navigate to the path where the DB backup file is located. After this, click on the Restore option. The backup would get restored into the database you selected. Under the Tables section, you should see an entry for the table countries -

Table countries under Tables section

Feel free to execute a select query on the table to see its contents.

Inspecting the countries table

Following is a snapshot of the countries table you just restored -

countries table

The table has different information about a total of 206 countries around the globe. The table has the following columns -

  • code which is the primary key of the table and denotes the universal country code for a given country
  • name i.e., name of a country
  • continent to which a country belongs
  • region viz. the geographical location where a country is located
  • surface_area of the country
  • indep_year i.e., the year in which a country got its independence
  • local_name of a country
  • gov_form i.e., the kind of government that is active in a country
  • capital of a country
  • cap_long i.e., longitude of the capital of a country
  • cap_lat i.e., latitude of the capital of a country

Let's now start filtering some records with the where clause.

Where clause examples

In SQL, where clause lets you filter records based on both textual and numeric values. You can compare these values, and the following comparison-based operations are supported -

  • = equal
  • <> and != not equal
  • < less than
  • > greater than
  • <= less than or equal to
  • >= greater than or equal to

It's advised to use <> rather than != as per the SQL standards.

Let's now get the record of the country where the code is AUS. The column you should compare here is the code column and the value to be compared is AUS. The query would be -

select * from countries where code = 'AUS';

And you get -

code is AUS result

Notice a couple of things here -

  • The where clause always comes after from.
  • In SQL, the strings quoted using single quotes.

Let's now see a numerical comparison. Let's get the records of the country which gained their independence (indep_year) after 1900 -

select * from countries where indep_year > 1901;

Countries gained independence after 1900

You get the records of a total of 145 countries which gained their independence after 1900. Take note of the comparator used here. The indep_yearcolumn is an integer in type. Hence, no quotes in the comparison.

You can connect multiple conditions in a where clause using AND and OR connectives. Following query gets you the records of those countries where continent = 'Asia' and indep_year > 1900 -

select * from countries where continent = 'Asia' and indep_year > 1900;

Connecting conditions using where clause

You get information about a total of 42 countries which are situated in the continent of Asia and gained their independence after 1900. Note that in the above query, you compared both textual and numerical values. As an exercise, you play with different combinations of AND and OR in the where clause.

Note: When you are combining AND and OR, don't forget to enclose each of the condition within parentheses. Otherwise, SQL will get confused to determine of the precedence the evaluation of the conditions.

With the following query, you get a total of 66 countries with condition as where (continent = 'Europe' and indep_year > 1900) or (continent = 'Asia' and indep_year > 1900).

select * from countries where
(continent = 'Europe' and indep_year > 1900)
(continent = 'Asia' and indep_year > 1900);

Connecting conditions using OR clause

You can even connect more than two conditions in a where clause -

select * from countries where continent = 'Asia' or indep_year > 1900 or surface_area > 652090;

Connecting more than two conditions using where clause

You get a total of 165 countries that are either situated in Asia continent or gained their independence after 1900 or having a surface area greater than 652090. Let's now convert the query into its AND variant where each of the conditions will be connected using AND.

select * from countries where continent = 'Asia' and indep_year > 1900 and surface_area > 652090;

Connecting conditions using AND

You will get a total of 9 countries in this case.

If you are comparing the same column value for more than once you will have to specify the column name each time. Refer the following query -

select * from countries where indep_year = 1947 or indep_year = 1912;

But the query select * from countries where indep_year = 1947 or 1912; is wrong.

You can pair up sub-queries in a where clause as well. Let's say you want to fetch the record of the country having the highest surface area, but you don't know what the maximum surface area obtained by a country is. The following query can come to rescue in situations like this -

select * from countries where surface_area in (select max(surface_area) from countries);

You should get the record of Russia.

Pair sub-queries using where clause


Congrats, you have completed this tutorial. Filtering query results with respect to conditions is definitely a vital skill, and this tutorial served you as an introduction to it. If you want to hone in your SQL skills even further, then you can check out the following DataCamp courses -

SQL for Joining Data

5 hours
Join two or three tables together into one, combine tables using set theory, and work with subqueries in PostgreSQL.
See DetailsRight Arrow
Start Course

Exploratory Data Analysis in SQL

4 hours
Learn how to explore what's available in a database: the tables, relationships between them, and data stored in them.

Introduction to Relational Databases in SQL

4 hours
Learn how to create one of the most efficient ways of storing data - relational databases!
See all coursesRight Arrow
Data Science Concept Vector Image

How to Become a Data Scientist in 8 Steps

Find out everything you need to know about becoming a data scientist, and find out whether it’s the right career for you!
Jose Jorge Rodriguez Salgado's photo

Jose Jorge Rodriguez Salgado

12 min

How to Become a Data Analyst in 2023: 5 Steps to Start Your Career

Learn how to become a data analyst and discover everything you need to know about launching your career, including the skills you need and how to learn them.
Elena Kosourova 's photo

Elena Kosourova

18 min

What is SQL Used For? 7 Top SQL Uses

Discover the uses of SQL in industries and specific jobs. Plus, learn why the SQL language is so versatile and in demand.
Natassha Selvaraj's photo

Natassha Selvaraj

11 min

DC Data in Soccer Infographic.png

How Data Science is Changing Soccer

With the Fifa 2022 World Cup upon us, learn about the most widely used data science use-cases in soccer.
Richie Cotton's photo

Richie Cotton

Sports Analytics: How Different Sports Use Data Analytics

Discover how sports analytics works and how different sports use data to provide meaningful insights. Plus, discover what it takes to become a sports data analyst.
Kurtis Pykes 's photo

Kurtis Pykes

13 min

How to Write a Bash Script: A Simple Bash Scripting Tutorial

Discover the basics of bash scripting and learn how to write a bash script.
Kurtis Pykes 's photo

Kurtis Pykes

5 min

See MoreSee More