SELECT without FROM, and real booleans

Two small changes you'll use every day on 26ai: you can drop FROM dual, and SQL finally has a BOOLEAN type with TRUE and FALSE. Before, every Oracle schema invented its own: 'Y'/'N', 1/0, 'T'/'F'... and every query had to remember which.

Part 1: no more DUAL

The test case

How many overs is 120 balls, and how many balls are there in 20 overs?

Way 1: no FROM at all

26ai only

select 120 / 6 as overs, 20 * 6 as balls;

Way 2: FROM DUAL, the 19c way

select 120 / 6 as overs, 20 * 6 as balls from dual;

DUAL is a real one-row table that has been in Oracle since the beginning, so that a SELECT always had something to select from. It still works on 26ai, so old code doesn't break. DUAL has always been the twelfth man: it's there in every match and never gets to bat.

Part 2: boolean expressions as columns

On 26ai a comparison is a value you can select, like a number.

The test case

For matches 7 to 12, did the team that won the toss also win the match?

Match 10 was a tie, so nobody won it, and the answer for it is unknown: null.

Way 1: select the comparison

26ai only

select match_id,
       toss_winner_id = winner_id as toss_won_match
from   cr_matches
where  match_id between 7 and 12
order  by match_id;

toss_winner_id = winner_id is TRUE, FALSE, or null when winner_id is null. Comparisons with null are never true or false: they're unknown. That's the same three-valued logic WHERE has always used. Now you can see it.

Way 2: CASE, the 19c way

select match_id,
       case when winner_id is null              then null
            when toss_winner_id = winner_id    then 'TRUE'
            else                                    'FALSE'
       end as toss_won_match
from   cr_matches
where  match_id between 7 and 12
order  by match_id;

The first WHEN matters. Without it the tie would fall through to ELSE and come out as 'FALSE', which is a different claim from "unknown". The 26ai way gets that right for free.

Part 3: BOOLEAN columns

Setup

drop table if exists cr_match_facts;

Setup

create table cr_match_facts as
select match_id, season,
       toss_winner_id = winner_id as toss_won_match,
       win_type = 'TIE'           as was_tie
from   cr_matches;

That CREATE TABLE ... AS SELECT makes two BOOLEAN columns from the comparisons. Run describe cr_match_facts (or look at the table in SQL Workshop) and they say BOOLEAN.

The test case

How many times per season did the toss winner win the match?

Way 1: the column is the condition

26ai only

select season, count(*) as toss_winner_won
from   cr_match_facts
where  toss_won_match
group  by season
order  by season;

A boolean column needs no = 'Y': WHERE toss_won_match reads like English.

Way 2: compare with TRUE

26ai only

select season, count(*) as toss_winner_won
from   cr_match_facts
where  toss_won_match = true
group  by season
order  by season;

Same thing, more words. Some teams prefer it because it reads the same as other comparisons.

Way 3: the 19c way, from the original table

select season, count(*) as toss_winner_won
from   cr_matches
where  toss_winner_id = winner_id
group  by season
order  by season;

Part 4: the null trap, and IS NOT TRUE

The test case

How many times per season did the toss winner not win? Count the tie: the toss winner didn't win it either.

Way 1: IS NOT TRUE

26ai only

select season, count(*) as toss_winner_didnt_win
from   cr_match_facts
where  toss_won_match is not true
group  by season
order  by season;

IS NOT TRUE is true for FALSE and for null. That's exactly "didn't win, including the tie".

The tempting WHERE NOT toss_won_match is wrong here: NOT null is still null, so the tie drops out and 2025 shows 4 instead of 5. Same with = false.

Way 2: LNNVL, the 19c way

select season, count(*) as toss_winner_didnt_win
from   cr_matches
where  lnnvl(toss_winner_id = winner_id)
group  by season
order  by season;

LNNVL(condition) is Oracle's long-standing answer to the same problem: it's true when the condition is false or unknown. It has been around since Oracle 10g and it's still the neatest 19c way to say "is not true".

Way 3: spell it out

select season, count(*) as toss_winner_didnt_win
from   cr_matches
where  toss_winner_id <> winner_id
or     winner_id is null
group  by season
order  by season;

19c and 26ai

Gotchas

• Booleans have three values. TRUE, FALSE and null. Decide what null means for your question before you write NOT.
• Tools differ in how they show booleans. SQL Workshop and SQLcl print TRUE/FALSE; some older drivers see 1/0. Our dataset sticks to NUMBER flags (is_six and friends) so it runs on 19c; on 26ai you could make them BOOLEAN, but then sum(is_six) stops working.
• PL/SQL had BOOLEAN long before SQL did. On 19c a PL/SQL function returning BOOLEAN can't be called from SQL; on 26ai it can.