LeetCode 595 - Big Countries
Database Language: SQL Server
Difficulty: Easy
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 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 SQL Server for this query:
|--Clustered Index Scan(OBJECT:([leetcode].[dbo].[World].[PK_World]),
WHERE:([leetcode].[dbo].[World].[area]>=(3000000) OR [leetcode].[dbo].[World].[population]>=(25000000)))
And here's the fastest run time for this query:
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:
The generated query plan is a little bit different than the query plan for the first query:
|--Clustered Index Scan(OBJECT:([leetcode].[dbo].[World].[PK_World]),
WHERE:([leetcode].[dbo].[World].[population]>=(25000000) OR [leetcode].[dbo].[World].[area]>=(3000000)))
There have been a lot of discussions in the SQL Server community about whether SQL Server 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 SQL Server performs a "short-circuit` when evaluating the `WHERE` clause that involves the `OR` operator.
Related Articles: