Understanding comparison operators on columns Consider this dummy_data:




number




1


2


3


4


5




I want to return all the rows from dummy_data where column number is below a certain value.
Came up with this:
WITH
  dummy_data AS
  (
    (SELECT 1 AS number) UNION (SELECT 2) UNION (SELECT 3) UNION (SELECT 4) UNION (SELECT 5)
  )
  , some_selection AS
  (
    (SELECT 3 AS number) -- in my use case, this query might return nothing...
    UNION
    (SELECT 0 AS number) -- ...so I'm using the union to 0 to sort of coalesce.
  )
SELECT
  *
FROM
  dummy_data
  , some_selection
WHERE
  dummy_data.number &lt; some_selection.number
;

Which returns:




number
number




1
3


2
3




So this seems to work like I intended, but I don't understand enough what's going on to know if I can rely on this code.
Can I be guaranteed that the comparison is always based on the max value of some_selection.number? Or should I add an intermediate step to explicitly extract max(some_selection.number)?
i.e. is the comparison based on the whole column or just on a single semi-random cell?
i.e. I'm worried the comparison might at times be performed on &lt; 0 which would return very different results.
It's probably in the Postgres manual somewhere, but I could not find the relevant section.
What you have done is performed a CROSS JOIN and then filtered on the result of that CJ! What a CROSS JOIN does is combine every field in the first table with every field in the second and is not frequently very useful - but not always unproductive!
Take the first small example:  (a PostgreSQL fiddle for all of the code in this answer is available here)
CREATE TABLE x (y INT, z TEXT); INSERT INTO x VALUES (1, 'h'), (2, 'i');
CREATE TABLE a (b INT, c TEXT); INSERT INTO a VALUES (8, 'r'), (9, 's');

Then:
SELECT x.y, x.z, a.b, a.c FROM x, a;

Result:
y   z   b   c
1   h   8   r
1   h   9   s
2   i   8   r
2   i   9   s

The systax below (called ANSI join - also, see the wiki):
SELECT * FROM x CROSS JOIN a;

is perhaps better because you're being explicit about your requirements (see fiddle).
Things are perhaps easier to understand if you eliminate the extraneous SQL from your statement above. In this case, I specifically mean your use of the COMMON TABLE EXPRESSION (CTE - aka the WITH clause) - instead, I'd do the following:

create the tables separately as follows:
 CREATE TABLE dat (d_num INT NOT NULL);
 INSERT INTO dat VALUES (1), (2), (3);


and
CREATE TABLE rand (r_num INT NOT NULL);
INSERT INTO rand VALUES (-23), (5), (3), (1);



So, now perform the CROSS JOIN with no filter. Just a note - you should be using ANSI joins - see this article from the excellent oracle-base site.
SELECT 
  d.d_num AS dn, 
  r.r_num AS rn 
FROM dat d, rand r
ORDER BY dn, rn;

Which can be rewritten:
SELECT 
  d.d_num AS dn, r.r_num AS rn FROM dat d
CROSS JOIN rand r
ORDER BY dn, rn;

Result:
dn     rn
 1    -23
 1      1
 1      3      &lt;&lt;---- rn &gt; dn
 1      5      &lt;&lt;---- rn &gt; dn
 2    -23
 2      1
 2      3      &lt;&lt;---- rn &gt; dn
 2      5      &lt;&lt;---- rn &gt; dn
 3    -23
 3      1
 3      3
 3      5      &lt;&lt;---- rn &gt; dn
 
12 rows

So, now we filter (keeping ANSI syntax):
SELECT 
  d.d_num AS dn, r.r_num AS rn 
FROM dat d
CROSS JOIN rand r
WHERE d.d_num &lt; r.r_num
ORDER BY dn, rn;

Result:
dn  rn
 1   3
 1   5    -- &lt;&lt;--- note 5 = MAX(r_num)
 2   3
 2   5    -- &lt;&lt;--- note 5 = MAX(r_num)
 3   5    -- &lt;&lt;--- note 5 = MAX(r_num)

 5 rows

And we can see that these 5 rows correspond to the 5 rows from the original CROSS JOIN.
So, to answer your question:

Can I be guaranteed that the comparison is always based on the max
value of some_selection.number? Or should I add an intermediate step
to explicitly extract max(some_selection.number)?

Your SQL will pick every number combination where ANY number in the first field is less than ANY number in the second field. This will pick any number that is less than the MAX() of field 2, but also any other combination where field 2 is greater than field 1.
If you want to just pick ANY number in the first table is less than the MAX() of the second table, you need to further refine your filter using a sub-SELECT as follows:
SELECT 
  d.d_num AS dn, r.r_num AS rn 
FROM dat d
CROSS JOIN rand r
WHERE d.d_num &lt; r.r_num
AND r.r_num = (SELECT MAX(r_num) FROM rand)  &lt;&lt;--- New condition!
ORDER BY dn, rn;

Result:
dn  rn
 1   5
 2   5
 3   5

And we can see that this result corresponds to the 3 records where 5 is the rn above.
A few words of advice:

make your initial SQL as simple as possible - using the WITH clause to generate data (IMHO) made your SQL more complex and didn't contribute to understanding.

use table and column aliases to make your SQL more readable.

when testing any of your solutions, always try to use weird outlier values (see -23 above) - I had 17 as well as 5, but the number of results of the CJ started to become too large - I just needed one value that was greater than all the values in the dat table.

EVERY SQL statement should (I would say MUST) have an ORDER BY clause - leaving this out is one of the biggest factors leading to confusion with results and what record in table A corresponds to what record in table B. I believe that in this case, using ORDER BY helps comprehension!

you used a variable called NUMBER in your example - you should never use SQL keywords as table or column names. This avoids confusion with error messages, aids with debugging and makes any query more portable - different systems escape keywords in different ways!

always include your server (and version) in your questions - for this case, the SQL would have worked for any server, but answers can frequently depend on these factors.

persist with the ANSI syntax (do a search for sql ansi joins versus old style join) - it seems strange at first, but is worth the effort!


p.s. bon courage avec ton anglais - qui n'est pas mauvais du tout d'ailleurs!