Learning SQL Subqueries as a Beginner: What Made Sense and What Didn’t

Today I stopped just watching SQL videos and finally went hands-on.

Instead of passively following tutorials, I practiced SQL subqueries directly on SQLZoo, specifically the “SELECT in SELECT” section, which focuses on using subqueries to write more expressive and powerful queries.

This is part of me learning SQL, PostgreSQL, and database fundamentals from scratch, and documenting the process honestly including where things don’t click yet.

What I Practiced: SELECT Inside SELECT (Subqueries)

The core idea of this section is simple:

Use the result of one SELECT query inside another SELECT.

In practice, this lets you write queries like:

• “Find values bigger than X”
• “Find values relative to another row”
• “Compare one group against another”

The table used throughout looks like this:

world(name, continent, area, population, gdp)

Exercises I Solved (1–6)

I was able to solve Exercises 1–6, which helped me recognize common subquery patterns.

1. Bigger than Russia

Find countries with a population larger than Russia:

SELECT name
FROM world
WHERE population >
  (SELECT population FROM world WHERE name = 'Russia');

This was my first “aha” moment, the inner query returns a single value, and the outer query compares against it.

1. Richer than the UK (Per Capita GDP)

SELECT name
FROM world
WHERE gdp / population >
  (SELECT gdp / population FROM world WHERE name = 'United Kingdom')
  AND continent = 'Europe';

Here, the subquery calculates a value instead of just returning a column.

1. Neighbours of Argentina and Australia (Same Continent)

SELECT name, continent
FROM world
WHERE continent IN (
  SELECT continent
  FROM world
  WHERE name = 'Argentina' OR name = 'Australia'
)
ORDER BY name;

This introduced IN, which made subqueries feel much more flexible.

1. Between the UK and Germany (Population Range)

SELECT name, population
FROM world
WHERE population >
  (SELECT population FROM world WHERE name = 'United Kingdom')
AND population <
  (SELECT population FROM world WHERE name = 'Germany');

Multiple subqueries, same table still readable.

1. Percentages of Germany’s Population

SELECT
  name,
  CONCAT(
    ROUND(
      population / (SELECT population FROM world WHERE name = 'Germany') * 100
    ),
    '%'
  ) AS percentage
FROM world
WHERE continent = 'Europe';

This one felt very “real-world”, calculating relative values using a subquery.

1. Bigger Than Every Country in Europe (Using ALL)

SELECT name
FROM world
WHERE gdp > ALL (
  SELECT gdp
  FROM world
  WHERE continent = 'Europe' AND gdp > 0
);

This introduced ALL, which lets a value be compared against every result from a subquery.

Where I Got Stuck: Correlated Subqueries

I hit a wall at Exercise 7.

The Question

Find the largest country (by area) in each continent.

The provided solution looked like this:

SELECT continent, name, area
FROM world x
WHERE area >= ALL (
  SELECT area
  FROM world y
  WHERE y.continent = x.continent
);

And this is where my brain paused.

What Confused Me (Honestly)

• What are x and y?
• Why does the inner query reference the outer query?
• How is this different from the earlier subqueries?

This is my first real encounter with a correlated subquery, where the inner query depends on each row of the outer query.

My current understanding (still forming):

x and y are aliases for the same table

For each row in world x, the subquery checks:

“Is this country’s area the largest within its continent?”

The inner query runs once per row in the outer query

It almost clicks but not fully yet. I’m deliberately stopping here instead of forcing it, and I’ll revisit this tlater with a fresh mind.

Why I’m Sharing This (Even Though I’m Stuck)

I’m documenting this because:

• Subqueries look easy at first
• Then correlated subqueries quietly change the rules
• And beginners often hit this exact wall but don’t talk about it

If you’re also learning SQL and subqueries feel weird you’re not alone.

What’s Next

• Reattempt Exercise 7 tomorrow
• Solidify my understanding of correlated subqueries
• Do more practice and finish the postgreSQL tutorial I started

I’m learning SQL step by step, not rushing it and sharing the process as honestly as possible.