Big Countries | Leetcode 595 | SQL_50 Study Plan

In this chapter of the SQL interview preparation series, we tackle LeetCode Problem 595: "Big Countries". This problem serves as an excellent introduction to filtering table data using conditional operators (WHERE, OR) and introduces alternative strategies such as set operations (UNION) for query construction.

1. Problem Overview and Objective

The goal of this problem is to query a table containing country data and filter out rows that satisfy specific size or demographic criteria.

• Input Table: A table named World containing five columns: name, continent, area, population, and gdp.
• Column Data Types:
  • name: varchar (Primary Key, meaning unique country names)
  • continent: varchar
  • area: int
  • population: int
  • gdp: bigint
• Output Required: Return the name, population, and area of all "big countries".
• Ordering: The result can be returned in any order.

2. Defining a "Big Country" (The Business Rule)

According to the problem statement, a country is classified as "big" if it satisfies at least one of the following conditions:

1. Area Condition: Its area is at least $3,000,000$ (3 million square kilometers).
2. Population Condition: Its population is at least $25,000,000$ (25 million people).

Because the rule specifies "at least one", this represents a logical disjunction (OR) rather than a conjunction (AND). If either condition evaluates to true, the row must be included in the final result set.

3. Database Schema and Sample Data Flow

To visualize how data flows from the underlying database table through our filtering logic, consider the following tabular representation:

When our SQL query executes against this table:

World Table (Raw Rows)
   |
   | evaluates WHERE clause (Area >= 3,000,000 OR Population >= 25,000,000)
   v
Filtered Rows (Afghanistan, Algeria, etc.)
   |
   | projects specific columns (name, population, area)
   v
Final Output Result Set

4. Approach 1: Naive / Direct Approach Using OR

The most straightforward, readable, and performance-optimized approach for this problem is using a single SELECT statement paired with a WHERE clause combined with the OR logical operator.

SQL Implementation

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

Why This Approach Excels

• Efficiency: The database query optimizer evaluates the condition across the table in a single pass.
• Maintainability: The query is concise, highly readable, and straightforward for any reviewer or interviewer to understand instantly.
• Time Complexity: $\mathcal{O}(N)$ where $N$ is the number of rows in the World table, typically executed with a table scan or index scan.

5. Approach 2: Alternative Strategy Using UNION

An alternative way to solve this problem—often useful when exploring set theory operations in SQL—is to break the problem into two distinct queries and combine their results using the UNION operator.

Step-by-Step Logic

1. Query 1: Select countries where the area is at least $3,000,000$.
2. Query 2: Select countries where the population is at least $25,000,000$.
3. Combination: Combine both result sets using UNION.

Note: UNION automatically removes duplicate rows (unlike UNION ALL). This is crucial here because a country that satisfies both conditions (e.g., large area and large population) would otherwise appear twice in the combined result set.

SQL Implementation

SELECT 
    name, 
    population, 
    area 
FROM 
    World 
WHERE 
    area >= 3000000

UNION

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

6. Comparison of Approaches

7. Production Realities & Edge Cases

When writing queries like this in real-world production environments, keep the following engineering principles in mind:

• Null Handling: If area or population contains NULL values in a production database, conditional operators (>=) evaluate to UNKNOWN, excluding those rows. Always verify column nullability constraints.
• Index Utilization: If queries of this nature are executed frequently on millions of rows, consider adding composite or single-column indexes on area and population:
  SQL

  Copy

  CREATE INDEX idx_world_area_pop ON World(area, population);
  

• UNION vs UNION ALL: Always use UNION when you need set deduplication. If you are certain no country can ever overlap or duplicate, UNION ALL is faster because it avoids the sorting overhead required to remove duplicates.

8. Summary and Key Takeaways

• Problem Core: LeetCode 595 asks us to filter rows based on multiple numerical thresholds using an inclusive OR condition.
• Primary Key Awareness: The name column is designated as the primary key, guaranteeing unique country names within the table.
• WHERE Clause Filtering: Use standard comparison operators (>=) combined with logical operators (OR) for clean, efficient filtering.
• Set Operations (UNION): Alternative query structures like UNION can combine separate result sets while automatically filtering out duplicate rows.
• Deduplication Nuance: Remember that UNION performs a distinct operation, whereas UNION ALL retains all records including duplicates.
• Optimization Mindset: Always prefer the simplest, most direct SQL query (WHERE ... OR ...) unless architectural constraints dictate otherwise.