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:

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:

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: