Skip to content

LeetCode 595 - Big Countries

Database Language: MySQL

Difficulty: ⭐

Problem Description

Input

Table: World

Column Name Type
name varchar
continent varchar
area int
population int
gdp bigint

name is the primary key (column with unique values) for this table. Each row of this table gives information about the name of a country, the continent to which it belongs, its area, the population, and its GDP value.

Requirement

A country is big if:

  • it has an area of at least three million (i.e., 3000000 km2), or
  • it has a population of at least twenty-five million (i.e., 25000000).

Write a solution to find the name, population, and area of the big countries.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1

Input

World table:

name continent area population gdp
Afghanistan Asia 652230 25500100 20343000000
Albania Europe 28748 2831741 12960000000
Algeria Africa 2381741 37100000 188681000000
Andorra Europe 468 78115 3712000000
Angola Africa 1246700 20609294 100990000000
Output
name population area
Afghanistan 25500100 652230
Algeria 37100000 2381741
Explanation

SQL Schema

CREATE TABLE IF NOT EXISTS World (name VARCHAR(255) PRIMARY KEY, continent VARCHAR(255), area INT, population INT, gdp BIGINT);

TRUNCATE TABLE World;
INSERT INTO World (name, continent, area, population, gdp) values ('Afghanistan', 'Asia', '652230', '25500100', '20343000000');
INSERT INTO World (name, continent, area, population, gdp) values ('Albania', 'Europe', '28748', '2831741', '12960000000');
INSERT INTO World (name, continent, area, population, gdp) values ('Algeria', 'Africa', '2381741', '37100000', '188681000000');
INSERT INTO World (name, continent, area, population, gdp) values ('Andorra', 'Europe', '468', '78115', '3712000000');
INSERT INTO World (name, continent, area, population, gdp) values ('Angola', 'Africa', '1246700', '20609294', '100990000000');

Solutions

Coming up with the query for this question should be straight forward. Based on the sample output, out of the 5 columns in the World table, the required output columns are just the name, population and area columns. Since the output columns already match the column names of the World table, column aliases are not needed.

SELECT name, population, area
FROM World
name population area
Afghanistan 25500100 652230
Albania 2831741 28748
Algeria 37100000 2381741
Andorra 78115 468
Angola 20609294 1246700

To get the list of big countries, the following conditions will be used based on the description of the requirements above:

  • area >= 3000000
  • population >= 25000000

Since a country is considered big if either of this two conditions are met, so the OR logical operator will be used. The WHERE clause of the query will be as follows:

WHERE area >= 3000000 OR population >= 25000000

Putting them all together yields the following query:

# Final Solution Query
SELECT name, population, area
FROM World
WHERE area >= 3000000 OR population >= 25000000

Here's the query plan generated by MySQL for this query:

-> Filter: ((world.area >= 3000000) or (world.population >= 25000000))  (cost=0.75 rows=3) (actual time=0.042..0.050 rows=2 loops=1)
    -> Table scan on World  (cost=0.75 rows=5) (actual time=0.029..0.035 rows=5 loops=1)

And here's the fastest run time for this query:

  • Runtime: 232ms
  • Beats: 97.11% as of July 16, 2024

One may wonder, will the query be faster if the conditions were interchanged? So, instead of WHERE area >= 3000000 OR population >= 25000000, what if the conditions were interchanged into WHERE population >= 25000000 OR area >= 3000000? Will this query be faster especially if there are lot of rows in the table that will satisfy the first condition?

Using the following updated query:

SELECT name, population, area FROM World
WHERE population >= 25000000 OR area >= 3000000;

The fastest runtime is as follows, which was just a few milliseconds slower than the first version:

  • Runtime: 236ms
  • Beats: 94.95% as of July 16, 2024

The generated query plan is a little bit different than the query plan for the first query:

-> Filter: ((world.population >= 25000000) or (world.area >= 3000000))  (cost=0.75 rows=3) (actual time=0.250..0.269 rows=2 loops=1)
    -> Table scan on World  (cost=0.75 rows=5) (actual time=0.240..0.256 rows=5 loops=1)

There have been a lot of discussions in the MySQL community about whether MySQL performs a "short-circuit" when evaluating the WHERE clause that involves the OR operator and no "official" documentation can be found that neither confirms nor denies that MySQL performs a "short-circuitwhen evaluating theWHEREclause that involves theOR` operator.