Using subqueries in PostgreSQL

Question

I'm only about a day into using PostgreSQL and have what is pry a quite simple question. I want to create a left outer join to search for mismatch values. I'd like from the join create a subquery which selects the null values and then updates are based on this selection. What I have for the left outer join looks like this:

SELECT TableA.field1, TableA.field2, TableB.field3
FROM 
   TableA
LEFT OUTER JOIN TableB ON TableA.field1 = TableB.field1

This works for the join. However, I can't manage to figure out how to then use a subquery to return only rows where TableB.field1 is null.

score 1 · Accepted Answer · answered Aug 01 '14 at 20:49

1

You don't need a subquery:

SELECT TableA.field1, TableA.field2, TableB.field1
FROM TableA
   LEFT OUTER JOIN TableB ON TableA.field1 = TableB.field1
where tableB.field1 IS NULL;

Not sure what you mean with "and then updates a based on this selection" though

answered Aug 01 '14 at 20:49

Thanks, @a_horse_with_no_name that seems to work. What I had meant I would then calculate a value into a seperatefield based on the selection where TableB/field1 is NULL. – standard Aug 01 '14 at 20:55

Erwin Brandstetter · Answer 2 · 2022-08-09T00:55:37.797

Here is how you ...

Select rows which are not present in other table

I suppose you want to update rows based on this selection. Use the FROM clause to join more (derived) tables in an UPDATE:

UPDATE some_table t
SET    (field1,   field2,   field3)
   = (a.field1, a.field2, a.field3)
FROM (
   SELECT a.a_id, a.field1, a.field2, a.field3
   FROM   tablea a
   LEFT   JOIN tableb b USING (field1)
   WHERE  b.field1 IS NULL
   ) a
WHERE t.a_id = a.a_id;  -- join on the connecting column

Using the short syntax variant to update multiple columns.

Using subqueries in PostgreSQL

2 Answers2

Linked