0

I have a table with a jsonb column, and I wanted to select from this table with the contents of that column extracted into columns:

CREATE TEMPORARY TABLE stuff (
    name text,
    contents jsonb
);

INSERT INTO stuff (name, contents)
    VALUES ('hello', '{"x": "hello", "y": "postgres", "z": "rdbms"}'::jsonb);

INSERT INTO stuff (name, contents)
    VALUES ('hello', '{"x": "hello2", "y": "postgres2", "z": "rdbms2"}'::jsonb);

CREATE OR REPLACE FUNCTION pg_temp.extract_stuff (s jsonb, out x text, out y text, out z text)
    RETURNS record
AS $$
BEGIN
    x = s ->> 'x';
    y = s ->> 'y';
    z = s ->> 'z';
END;
$$
LANGUAGE plpgsql;

I was screwing around and somehow landed on this statement, which works perfectly and looks very intuitive:

SELECT
    name,
    foobar.x,
    foobar.y,
    foobar.z
FROM
    stuff,
    pg_temp.extract_stuff (contents) foobar;

 name  |   x    |     y     |   z    
-------+--------+-----------+--------
 hello | hello  | postgres  | rdbms
 hello | hello2 | postgres2 | rdbms2

However, I'm not sure I understand how this works. Is it shorthand for an implicit inner join of some sort? What is the limitation of this type of syntax (can there be multiple physical tables, etc)?

2

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... ).

  • Ok, thank you for the advice, I'll use the cross join syntax. Though, if I understand correctly, cross joins should return N * M rows when the things being joined has N and M rows. Should I think of the function call on the right-hand-side of the join as producing a single row (M=1), which is different for each row on the left? – user3243135 Jan 16 '18 at 2:17
  • 1
    LATERAL means perform the join for every row.. It's the same as SQL Server's non-standard CROSS APPLY. Whether or not that function returns one row, I have no idea. I would hope it would, but if returns 0-rows, you'll get 0-rolls in the result set. If it returns more than one row, you'll grow your result set. – Evan Carroll Jan 16 '18 at 2:19
  • 1
    Wow, thanks for expanding the answer so thoroughly, it'll take me a bit to digest. I'd bounty you some points but I don't have enough reputation on this stackexchange. – user3243135 Jan 16 '18 at 4:13
  • 2
    " In PostgreSQL the Implicit syntax has a different precedent, and it currently serves as an optimization fence." I don't think that the page you linked makes such a claim. It even says so: "Explicit inner join syntax (INNER JOIN, CROSS JOIN, or unadorned JOIN) is semantically the same as listing the input relations in FROM, so it does not constrain the join order." – ypercubeᵀᴹ Jan 16 '18 at 10:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.