selecting from multiple tables in SQL without a join

Question

Complete SQL novice, trying to make sense of the following TPC-H query:

select
        l_orderkey,
        sum(l_extendedprice * (1 - l_discount)) as revenue,
        o_orderdate,
        o_shippriority
from
        customer,
        orders,
        lineitem
where
        c_mktsegment = ':1'
        and c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate < date ':2'
        and l_shipdate > date ':2'
group by
        l_orderkey,
        o_orderdate,
        o_shippriority
order by
        revenue desc,
        o_orderdate;
:n 10

I was under the impression that you can only select from different tables after a join. However I don't see any joins here. Is it implicit?

score 1 · Accepted Answer · answered Jun 27 '21 at 21:22

The comma is archaic syntax for the CROSS JOIN. It is essentially the same thing as a Cartesian product (although the scoping rules in the FROM clause are a bit different).

The filter conditions in the WHERE clause happen to turn this Cartesian product into an inner join, but that is a function of the filtering after the FROM clasue.

You should just forget that you every learned that this syntax works. Use explicit JOIN syntax, even for CROSS JOIN. JOIN has been part of SQL syntax -- literally -- for decades and it was added for very good reasons.

selecting from multiple tables in SQL without a join

1 Answers1