VALUES: rows made on the spot

Sometimes you need a few rows that aren't in any table: a lookup of codes, some test data, the phases of an innings. 26ai lets you write them straight into the query with VALUES, and insert several rows with one INSERT. On 19c you stack SELECT ... FROM dual with UNION ALL.

Part 1: a lookup in the query

The test case

For 2026, show each kind of extra with its full name, how many there were and how many runs they gave away. Include every kind, even if there were none.

Way 1: a table value constructor

26ai only

select x.code, x.extra_name,
       count(b.extra_type)          as deliveries,
       nvl(sum(b.runs_extras), 0)   as runs
from   (values ('WD', 'Wide'),
               ('NB', 'No-ball'),
               ('LB', 'Leg-bye'),
               ('B',  'Bye')) x (code, extra_name)
left   join cr_balls_v b on b.extra_type = x.code and b.season = 2026
group  by x.code, x.extra_name
order  by deliveries desc, x.code;

(VALUES (...), (...)) x (code, extra_name) is a small table that exists only in this query, like a net session: there when you need it, gone when you're done. The names after the alias are its column names. It joins like any table.

Way 2: UNION ALL from DUAL (19c)

select x.code, x.extra_name,
       count(b.extra_type)          as deliveries,
       nvl(sum(b.runs_extras), 0)   as runs
from   (select 'WD' as code, 'Wide'    as extra_name from dual union all
        select 'NB',         'No-ball'               from dual union all
        select 'LB',         'Leg-bye'               from dual union all
        select 'B',          'Bye'                   from dual) x
left   join cr_balls_v b on b.extra_type = x.code and b.season = 2026
group  by x.code, x.extra_name
order  by deliveries desc, x.code;

It works everywhere, it's just noisier. The column names come from the aliases in the first SELECT.

Way 3: a WITH clause (19c)

with extra_kinds (code, extra_name) as (
  select 'WD', 'Wide'    from dual union all
  select 'NB', 'No-ball' from dual union all
  select 'LB', 'Leg-bye' from dual union all
  select 'B',  'Bye'     from dual
)
select x.code, x.extra_name,
       count(b.extra_type)          as deliveries,
       nvl(sum(b.runs_extras), 0)   as runs
from   extra_kinds x
left   join cr_balls_v b on b.extra_type = x.code and b.season = 2026
group  by x.code, x.extra_name
order  by deliveries desc, x.code;

Naming the lookup at the top keeps the main query tidy. The column list after the name (extra_kinds (code, extra_name)) works on 19c too.

Two details make "every kind, even with none" work: the LEFT JOIN, and the season filter inside the ON rather than in WHERE (page 0.3 explains why). count(b.extra_type) counts only matched rows, so a kind with no deliveries shows 0.

Part 2: several rows in one INSERT

The test case

Make a small table of the three phases of an innings (powerplay 1-6, middle 7-16, death 17-20) and read it back.

The 26ai table is filled with one multi-row INSERT:

Setup

drop table if exists cr_phases;

Setup

create table cr_phases (phase varchar2(10) primary key, from_over number(2), to_over number(2));

Setup

insert into cr_phases (phase, from_over, to_over)
values ('Powerplay', 1, 6),
       ('Middle',    7, 16),
       ('Death',    17, 20);

The 19c copy is filled with INSERT ALL, which has been around since 9i:

Setup

begin
  execute immediate 'drop table cr_phases_19c purge';
exception when others then
  if sqlcode != -942 then raise; end if;    -- -942: table doesn't exist yet
end;
/

Setup

create table cr_phases_19c (phase varchar2(10) primary key, from_over number(2), to_over number(2));

Setup

insert all
  into cr_phases_19c (phase, from_over, to_over) values ('Powerplay', 1, 6)
  into cr_phases_19c (phase, from_over, to_over) values ('Middle',    7, 16)
  into cr_phases_19c (phase, from_over, to_over) values ('Death',    17, 20)
select * from dual;

(The INSERT ALL needs a SELECT at the end, even though nothing is selected from it. DUAL again, still twelfth man.)

Way 1: read the 26ai table

26ai only

select phase, from_over, to_over from cr_phases order by from_over;

Way 2: read the 19c table

select phase, from_over, to_over from cr_phases_19c order by from_over;

Both tables hold the same three rows. On 19c you could also just write three INSERT statements; INSERT ALL saves round trips when you run it from a client over a network.

19c and 26ai

Gotchas

• All rows of a VALUES list need compatible types in each column, like UNION ALL.
• Big lists belong in a table. VALUES is for a handful of rows. For hundreds, load a table.
• The 19c DROP workaround above swallows only error -942 ("table or view does not exist"). Catching others and ignoring everything would hide real problems. On 19.28 and later you can write drop table if exists instead (page 12.7).