Quite a useful construct is WITH. This lets you name a subquery result for later use in your main query. Thus the queries are much easier to understand.
It may also let you override a catastrophic query plan:
WITH withrvs AS (SELECT TOP 200 ra, dec, source_id, a.radial_velocity, b.rv as raverv FROM gaia.dr3lite AS a JOIN rave.main AS b ON ( DISTANCE(a.ra, a.dec, b.raj2000, b.dej2000) < 1/3600.)) SELECT * FROM gdr3spec.spectra JOIN withrvs USING (source_id)
Each ADQL query will be translated in a sequence of steps the database will process in order to perform the whole query. This query plan may switch the order of steps which were defined in the scripts to enhance the performance. The query planner bases this plan on estimates of table sizes and the “selectivities” of predicates (basically: how often they will be true). If they get these estimates wrong, the query plans can be wrong, too, sometimes catastrophically so. In these cases, forcing the planner using CTEs may save the day.
In our example, we crossmatch Gaia and Rave and pull radial velocities from both. Then we want to add BP/RP spectra (which here come in arrays) with a simple join on the Gaia source id; since at least in 2022, the backend database gets the estimate of the selectivity of the distance condition grossly wrong, without the CTE the database would first match the 200 million rows of of the Gaia spectra to the Gaia catalogue before turning to the half a million rave rows, turning a reasonably fast query into a matter of hours.