Implicit vs Explicit JOIN Syntax
It's called a SQL-89 Implicit (Cross) Join. You should prefer the more modern Explicit syntax. In PostgreSQL the Implicit syntax has a different precedent, and it currently serves as an optimization fence. What you actually want is a CROSS JOIN and because you're using the variables from the left table in a function call what you want is a CROSS JOIN LATERAL.
SELECT
name,
foobar.x,
foobar.y,
foobar.z
FROM stuff
CROSS JOIN LATERAL pg_temp.extract_stuff (contents) AS foobar;
The reason for the preference is style: the newer syntax allows for syntactical binding between the JOIN-clause and the condition that provides the JOIN's selectivity. While technically this is no different, it makes it easier to maintain and read. The SQL-89 method is to stuff the JOIN-selectivity in the WHERE-condition. This makes maintaining the query a ton easier.
SELECT f.g
FROM f AS f1
JOIN f2 USING (fid);
JOIN f3 ON f3.fkey_id = f.id
WHERE f.x = 15;
Versus,
SELECT f.g
FROM f, f2, f3
-- Potentially hundreds of lines.
WHERE f.x = 15 -- no
AND f.id = f2.id -- specific
AND f3.fkey_id = f.id; -- order
Expanding the scope of the question to [INNER] JOIN, you may want to stare at this example for a bit,
SQL-92: Explicit JOIN with Explicit INNER
SELECT *
FROM a
INNER JOIN b ON a.id = b.id
INNER JOIN c ON b.ref = c.id;
SQL-92: Explicit JOIN with Implicit INNER
SELECT *
FROM a
JOIN b ON a.id = b.id
JOIN c ON b.ref = c.id;
SQL-89: Never use without reason
SELECT *
FROM a, b, c
WHERE a.id = b.id AND b.ref = c.id;
SQL-92: CROSS JOIN with JOIN's selectivity-clause written as a WHERE-clause. Some would say this is "flattened".
SELECT *
FROM a
CROSS JOIN b
CROSS JOIN c
WHERE a.id = b.id AND b.ref = c.id
SQL-92: Explicit JOIN with goofy order (may be useful for optimizing, depending on RDBMS)
SELECT *
FROM a
JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
Unless you have a reason, you should always prefer the first or second syntax.
USING clause
USING is shorter (less to type), and more terse. This can also save you some work and seems less problematic from my perspective.
Column Listing
USING adds the nicety of not reproducing the column when * is expanded. Let's again look at some examples,
This will return one row with one column: (a=1). It can do this because its t.a and g.a are marked as the same with USING and as the equijoin condition they must be equal.
-- Returns one column with a=1
SELECT *
FROM ( VALUES(1) ) AS g(a)
INNER JOIN ( VALUES(1) ) AS t(a)
USING (a);
This will return one row with two columns: (a=1,a=1).
-- Returns two columns with a=1,a=1
SELECT *
FROM ( VALUES(1) ) AS g(a)
INNER JOIN ( VALUES(1) ) AS t(a)
ON t.a = g.a;
That's nice, but even if you never use a there is something to be had with USING.
This will gladly work
SELECT a=1
FROM ( VALUES(1) ) AS g(a)
INNER JOIN ( VALUES(1) ) AS t(a)
USING (a);
This dies: requires explicitly picking an 'a' to disambiguate
SELECT a=1
FROM ( VALUES(1) ) AS g(a)
INNER JOIN ( VALUES(1) ) AS t(a)
ON t.a = g.a;
Mixed Use
Some detractors think it's nasty to mix USING with ON. I don't think so, but take a look for yourself.
SQL-92: With USING sugar, Explicit JOIN with implicit INNER token
SELECT *
FROM a
JOIN b USING (id)
JOIN c ON b.ref = c.id;
SQL-92: Without USING sugar, Explicit INNER JOIN
SELECT *
FROM a
INNER JOIN b ON a.id = b.id
INNER JOIN c ON b.ref = c.id;
Convention
USING establishes a convention from the spec of making your equijoins on id-columns that are globally unique: by this I mean it kills off the all too common convention of naming every table's surrogate key id. Generally, I think that's a admirable goal (or side effect) when possible.
Anti- JOINs
As a final note on SQL-92. SQL-92 provides the EXISTS predicate. This gives you the option to write an anti-join like this,
SELECT *
FROM t1
LEFT OUTER JOIN t2 USING (id)
-- could
-- be
-- lots of stuff
WHERE t2.id IS NULL;
Or, like this
SELECT *
FROM t1
WHERE NOT EXISTS (
SELECT 1
FROM t2
WHERE t1.id = t2.id
);
For the same reasons presented earlier, the option with NOT EXISTS is better then WHERE t2.id IS NULL: it keeps the joining-conditionals semantically linked. And it clears up two otherwise potential areas of confusion. For instance
WITH t(txt,n) AS ( VALUES ('foo',null) )
SELECT txt
FROM t AS t1
LEFT OUTER JOIN t AS t2
USING (n)
WHERE t2.n IS NULL;
Versus,
WITH t(txt,n) AS ( VALUES ('foo',null) )
SELECT txt
FROM t AS t1
WHERE NOT EXISTS (
SELECT 1
FROM t AS t2
WHERE t1.n = t2.n
);
Without the NOT EXISTS syntax, it's not immediately clear whether the intent is to achieve an equijoin on NULL, or to select where there is no match. Though they get planned the same the NOT EXISTS predicate is more descriptive than an IS NULL predicate for the task.
Summary
My preference is for Explicit JOIN syntax with USING whenever possible. And to always write antijoins using NOT EXISTS ( SELECT 1... ).
