Introduction to the WHERE Clause in SQL

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 setting up a PostgreSQL database and manually creating a table containing country data. After this is set up, 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.

• Beginner's Guide to PostgreSQL
• Installing PostgreSQL on Windows and Mac OS X

Creating a Table in PostgreSQL

To follow along with this tutorial, you’ll need to create a table in PostgreSQL that contains information about countries around the world. This table will be used to demonstrate how the WHERE clause works in different scenarios.

Step 1: Create a new database

Open pgAdmin (or your preferred SQL interface) and create a new PostgreSQL database. You can name it something like world_data or anything else you prefer.

Step 2: Create and populate our table

Once your database is ready, open a new SQL query editor window and run the following SQL code. It will create a table called countries and insert a set of records covering a wide range of continents, independence years, and regions:

-- Create the countries table
CREATE TABLE countries (
    code VARCHAR(3) PRIMARY KEY,
    name VARCHAR(100),
    continent VARCHAR(50),
    region VARCHAR(100),
    surface_area NUMERIC,
    indep_year INTEGER,
    local_name VARCHAR(100),
    gov_form VARCHAR(100),
    capital VARCHAR(100),
    cap_long NUMERIC,
    cap_lat NUMERIC
);

-- Insert sample data into the countries table
INSERT INTO countries (code, name, continent, region, surface_area, indep_year, local_name, gov_form, capital, cap_long, cap_lat)
VALUES
('IND', 'India', 'Asia', 'Southern Asia', 3287263, 1947, 'Bharat', 'Federal Republic', 'New Delhi', 77.2090, 28.6139),
('PAK', 'Pakistan', 'Asia', 'Southern Asia', 881912, 1947, 'Pakistan', 'Federal Republic', 'Islamabad', 73.0551, 33.6844),
('CHN', 'China', 'Asia', 'Eastern Asia', 9596961, NULL, 'Zhongguo', 'People''s Republic', 'Beijing', 116.4074, 39.9042),
('VNM', 'Vietnam', 'Asia', 'South-Eastern Asia', 331212, 1945, 'Việt Nam', 'Socialist Republic', 'Hanoi', 105.8544, 21.0285),
('JPN', 'Japan', 'Asia', 'Eastern Asia', 377975, NULL, 'Nippon', 'Constitutional Monarchy', 'Tokyo', 139.6917, 35.6895),
('DEU', 'Germany', 'Europe', 'Western Europe', 357386, 1955, 'Deutschland', 'Federal Republic', 'Berlin', 13.4050, 52.5200),
('FRA', 'France', 'Europe', 'Western Europe', 551695, 843, 'France', 'Republic', 'Paris', 2.3522, 48.8566),
('RUS', 'Russia', 'Europe', 'Eastern Europe', 17098242, 1991, 'Rossiya', 'Federation', 'Moscow', 37.6173, 55.7558),
('UKR', 'Ukraine', 'Europe', 'Eastern Europe', 603500, 1991, 'Ukrayina', 'Republic', 'Kyiv', 30.5234, 50.4501),
('EGY', 'Egypt', 'Africa', 'Northern Africa', 1002450, 1922, 'Misr', 'Republic', 'Cairo', 31.2357, 30.0444),
('NGA', 'Nigeria', 'Africa', 'Western Africa', 923768, 1960, 'Nigeria', 'Federal Republic', 'Abuja', 7.4951, 9.0578),
('AUS', 'Australia', 'Oceania', 'Australia and New Zealand', 7682300, 1901, 'Australia', 'Federal Parliamentary Democracy', 'Canberra', 149.1245, -35.3075),
('USA', 'United States', 'North America', 'North America', 9833517, 1776, 'United States', 'Federal Republic', 'Washington, D.C.', -77.0369, 38.9072),
('CAN', 'Canada', 'North America', 'North America', 9984670, 1867, 'Canada', 'Constitutional Monarchy', 'Ottawa', -75.6972, 45.4215),
('BRA', 'Brazil', 'South America', 'South America', 8515767, 1822, 'Brasil', 'Federal Republic', 'Brasília', -47.9292, -15.7801),
('ARG', 'Argentina', 'South America', 'South America', 2780400, 1816, 'Argentina', 'Federal Republic', 'Buenos Aires', -58.3816, -34.6037);

The table has different information about a total of about 16 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 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.

Using the SQL WHERE Clause

In SQL, the 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:

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 1901:

SELECT * FROM countries WHERE indep_year > 1901;

You get the records of a total of 3 countries that gained their independence after 1901. 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 > 1901;

You get information about a total of three countries that are situated in the continent of Asia and gained their independence after 1901. 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 conditions within parentheses. Otherwise, SQL will get confused when determining the precedence of the evaluation of the conditions.

With the following query, you get a total of six countries.

SELECT * FROM countries WHERE
(continent = 'Europe' AND indep_year > 1900)
or
(continent = 'Asia' AND indep_year > 1900);

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;

Here, you get a total of 15 countries that are either situated in Asia or have gained their independence after 1901 or have a surface area greater than 652090.

Let's now convert the query into an AND variant, which is more restrictive:

SELECT * FROM countries WHERE continent = 'Asia' AND indep_year > 1901 AND surface_area > 652090;

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

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 the 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.

Conclusion

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