Subqueries: a query inside a query

A subquery is a SELECT in brackets used where a value or a table would go. Three kinds:

The test case

2026 batters who scored more than twice their team's average runs per batter. Show the team, the batter, their runs and the team average (one decimal).

Expected result

The one-man teams show up straight away: Karachi has only Faisal Qureshi over the line.

Way 1: a correlated subquery

with runs as (
  select p.team_id, p.player_name, sum(b.runs_batter) as runs
  from   cr_balls_v b
  join   cr_players p on p.player_id = b.batter_id
  where  b.season = 2026
  group  by p.team_id, p.player_name
)
select t.team_code, r.player_name, r.runs,
       (select round(avg(x.runs), 1) from runs x where x.team_id = r.team_id) as team_avg
from   runs r
join   cr_teams t on t.team_id = r.team_id
where  r.runs > 2 * (select avg(x.runs) from runs x where x.team_id = r.team_id)
order  by t.team_code, r.runs desc;

x.team_id = r.team_id ties each subquery to the outer row: "the average of this batter's team". The same subquery appears twice (once to show, once to filter); the optimizer usually works that out.

Way 2: AVG as a window function

with runs as (
  select p.team_id, p.player_name, sum(b.runs_batter) as runs
  from   cr_balls_v b
  join   cr_players p on p.player_id = b.batter_id
  where  b.season = 2026
  group  by p.team_id, p.player_name
)
select team_code, player_name, runs, round(team_avg, 1) as team_avg
from   (select t.team_code, r.player_name, r.runs,
               avg(r.runs) over (partition by r.team_id) as team_avg
        from   runs r
        join   cr_teams t on t.team_id = r.team_id)
where  runs > 2 * team_avg
order  by team_code, runs desc;

The average is computed once per team and written on every row, then filtered outside. One pass, no repetition: usually the better choice (Part 5).

Way 3: join to an aggregated inline view

with runs as (
  select p.team_id, p.player_name, sum(b.runs_batter) as runs
  from   cr_balls_v b
  join   cr_players p on p.player_id = b.batter_id
  where  b.season = 2026
  group  by p.team_id, p.player_name
)
select t.team_code, r.player_name, r.runs, round(a.team_avg, 1) as team_avg
from   runs r
join   (select team_id, avg(runs) as team_avg from runs group by team_id) a on a.team_id = r.team_id
join   cr_teams t on t.team_id = r.team_id
where  r.runs > 2 * a.team_avg
order  by t.team_code, r.runs desc;

Aggregate per team in an inline view, then join it back to the batters. The classic, portable form.

19c and 26ai

The same on both.

Gotchas

• A scalar subquery must return at most one row. Two rows give ORA-01427: single-row subquery returns more than one row; none gives null.
• Correlated subqueries can be slow if Oracle really runs them once per row. Window functions and joins usually scale better.
• IN (subquery) vs = (subquery): use IN whenever the subquery could return several rows.