PostgreSQL – Understanding [FROM x, y] Syntax

join;postgresql

I'm just getting started with Postgres. Reading this document I came across this query:

SELECT title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT 10;

I can understand everything in this query, except for this: FROM apod, ....

What does this , mean? I'm used to joins but not to multiple FROM statements separated by a comma.

I searched the net for no avail. After looking at it and thinking, it seems to me that it's declaring a variable called query so it can use it multiple times. But if this is true, what does it have to do with FROM?

Best Answer

It creates an implicit CROSS JOIN. It's the SQL-89 syntax.

Here I use values(1) and values(2) to create pseduo-tables (value tables) merely for examples. The thing after them t(x), and g(y) are called FROM-Aliases the character inside the parenthesis is the alias for the column (x and y respectively). You could just as easily create a table to test this.

SELECT *
FROM (values(1)) AS t(x), (values(2)) AS g(y)

Here is how you'd write it now.

SELECT *
FROM (values(1)) AS t(x)
CROSS JOIN (values(2)) AS g(y);

From there you can make this an implicit INNER JOIN by adding a conditional.

SELECT *
FROM (values(1)) AS t(x)
CROSS JOIN (values(1)) AS g(z)
WHERE x = z;

Or the explicit and newer INNER JOIN syntax,

SELECT *
FROM (values(1)) AS t(x)
INNER JOIN (values(1)) AS g(z)
  ON ( x = z );

So in your example..

FROM apod, to_tsquery('neutrino|(dark & matter)') query

This is essentially the same as the newer syntax,

FROM apod
CROSS JOIN to_tsquery('neutrino|(dark & matter)') AS query

which is actually the same, in this case, because to_tsquery() returns a row and not a set as,

SELECT title, ts_rank_cd(
  textsearch,
  to_tsquery('neutrino|(dark & matter)')
) AS rank
FROM apod
WHERE to_tsquery('neutrino|(dark & matter)') @@ textsearch
ORDER BY rank DESC
LIMIT 10;

However, the above could potentially cause to_tsquery('neutrino|(dark & matter)') to occur twice, but in this case it doesn't -- to_tsquery is marked as STABLE (verified with \dfS+ to_tsquery).

STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. (It is inappropriate for AFTER triggers that wish to query rows modified by the current command.) Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.

For a more complete comparison of the differences between SQL-89, and SQL-92, see also my answer here

Related Question