LeetCode 595 - Big Countries¶
Database Language: SQL Server
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 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:
- Runtime: 531ms
- Beats: 96.89% as of July 28, 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: 538ms
- Beats: 93.58% as of July 28, 2024
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-circuitwhen evaluating theWHEREclause that involves theOR` operator.