What kind of join is used in a Vertica UPDATE statement?

Question

Vertica has an interesting update syntax when updating a table based on a join value. Instead of using a join to find the update rows, it mandates a syntax like this:

UPDATE a
SET col = b.val
where a.id = b.id

(Note that this syntax is indeed mandated in this case, because Vertica prohibits us from using a where clause that includes a "self-join", that is a join referencing the table being updated, in this case a.)

This syntax is nice, but it's less explicit about the join being used than other SQL dialects. For example, what happens in this case?

UPDATE a
SET col = CASE 0 if b.id IS NULL ELSE b.val END
where a.id = b.id

What happens when a.id has no match in b.id? Does a.col not get updated, as though the condition a.id = b.id represented an inner join of a and b? Or does it get updated to zero, as if the condition were a left outer join?

Answer 1

I think Vertica uses the Postgres standard for this syntax:

UPDATE a
    SET col = b.val
    FROM b
    whERE a.id = b.id;

This is an INNER JOIN. I agree that it would be nice if Postgres and the derived databases supported explicit JOINs to the update table (as some other databases do). But the answer to your question is that this is an INNER JOIN.

I should note that if you want a LEFT JOIN, you have two options. One is a correlated subquery:

UPDATE a
    SET col = (SELECT b.val FROM b whERE a.id = b.id);

The other is an additional level of JOIN (assuming that id is unique in a):

UPDATE a
    SET col = b.val
    FROM a a2 LEFT JOIN
         b
         ON a2.id = b.id
    WHERE a.id = a2.id;