13. Filtering by country

In the previous lesson, we’ve filtered records from the users table by country:

SELECT *
FROM users
WHERE
  country = 'de'

What questions are popping up in your head at this moment? Probably something similar to these:

1. Why Germany has de value in the country column?
2. How do we know the user’s country?
3. What are country column values for other countries?

In this lesson, let’s answer these questions one by one.

Values in the country column

At the moment, there are ~250 countries in the world. Some of them have very long names like Saint Vincent and the Grenadines or South Georgia and the South Sandwich Islands (South Sandwich , really? ).

Quite often, organizations all over the world need to store country related information: customers address, delivery address, etc. It’s quite cumbersome to always refer to the country by its name (it literally cost paper or computer space). This is why the International Organization for Standardization (ISO) developed a standard – two-letter country codes to represent all countries.

Country codes in the users table

When users sign up on the web or via a mobile app, their browser (or the mobile app) sends a request to the company’s servers with their email and password. But that’s not all of it! User’s cookies (for web signups) and an IP address are also visible to the server.

The country is derived from an IP address via the process called IP Geolocation. It’s very simple, actually – there’s literally a table of all IP addresses and their locations. There are companies like Maxmind which maintain these tables up to date and sell them as a service.

To sum it up: every time someone signs up for Bindle we store not just their email and password, but also a country derived from the IP address.

Country codes

These are all country codes according to ISO 3166 standard:

Whoa, a lot of country codes!

We’ve touched enough on filters and even learned some tech specifics behind country codes. It’s time we do the first exercise!