SQL - Boolean Expression As Alias

 

SELECT player_name, season = exploded_season AS is_active
FROM nba_player_seasons 
CROSS JOIN UNNEST (sequence(1996, 2002)) AS t(exploded_season) 
WHERE player_name = 'Michael Jordan';

Please help me understand the 'season = exploded_season' in the above SQL? As I understand, such expression can not appear in SELECT.

 

ChatGPT said:

Great question! You're right to pause here — season = exploded_season AS is_active in the SELECT clause might look confusing at first glance.

Let’s break down this line:

SELECT player_name, season = exploded_season AS is_active

✅ What it actually means:

This is a boolean expression with an alias.

• season = exploded_season is a comparison — it checks whether the season column is equal to the value exploded_season (which comes from the UNNESTed sequence).

• AS is_active is just giving this boolean result a column alias.

So this line is saying:

“Return a column named is_active, which is TRUE if season = exploded_season, otherwise FALSE.”

---

🔎 So what is exploded_season?

Let’s look at the FROM clause:

CROSS JOIN UNNEST(sequence(1996, 2002)) AS t(exploded_season)

This means:

• You're generating a list of numbers from 1996 to 2002 using sequence().

• You're unnesting that array into rows, with each value aliased as t(exploded_season).

So exploded_season is each season from 1996 to 2002.

---

✅ Full query explained

SELECT player_name, season = exploded_season AS is_active
FROM nba_player_seasons 
CROSS JOIN UNNEST (sequence(1996, 2002)) AS t(exploded_season) 
WHERE player_name = 'Michael Jordan';

This query:

1. Filters the table for rows where player_name = 'Michael Jordan'.

2. Joins each such row to every season from 1996 to 2002 (using CROSS JOIN UNNEST).

3. Outputs two columns:

   • player_name

   • A boolean is_active, which is TRUE only when season = exploded_season.

---

📝 Example output

player_name	is_active
Michael Jordan	FALSE
Michael Jordan	TRUE
Michael Jordan	FALSE
...	...

 

---

Let me know if you'd like to adjust this to show the seasons explicitly or pivot the result to one row per player with columns per season.

 

You said:

Is such writing valid in all standard SQL or just supported by particular databases?

 

ChatGPT said:

Excellent follow-up. The expression:

SELECT season = exploded_season AS is_active

is not valid in all standard SQL — it's a feature supported only in some SQL dialects, especially those that allow boolean expressions directly in the SELECT clause.

---

🔍 Let’s break it down:

✅ Valid in these databases:

Database	Supports boolean_expr AS alias directly?	Notes
PostgreSQL	✅ Yes	Fully supports boolean expressions in SELECT, WHERE, GROUP BY, etc.
Trino/Presto	✅ Yes	Also supports UNNEST and inline booleans
BigQuery	✅ Yes	Allows SELECT a = b AS is_equal
SQLite	✅ Yes	Evaluates to 0 (false) or 1 (true)
MySQL	⚠️ Yes, but returns 0/1	Booleans are treated as integers
SQL Server	❌ No	You must use CASE WHEN instead
Oracle	❌ No	Booleans not directly selectable; must use CASE

 

---

🧱 In standard SQL (ISO SQL):

Standard SQL does not allow using a boolean expression like a = b directly in the SELECT list unless:

• It is wrapped in a CASE expression or

• The DBMS extends the standard to support it.

✅ Standard-compatible version:

SELECT CASE WHEN season = exploded_season THEN TRUE ELSE FALSE END AS is_active

This works everywhere.

---

✅ Recommendation:

If you're working in:

• PostgreSQL / Trino / BigQuery: season = exploded_season AS is_active is fine.

• SQL Server / Oracle: Use CASE instead.

• Portable code (multi-platform): Always use CASE WHEN.