PostgreSQL LEFT OUTER JOIN query syntax

Question

Lets say I have a table1:

  id      name
-------------
  1       "one"
  2       "two"
  3       "three"

And a table2 with a foreign key to the first:

id    tbl1_fk    option   value
-------------------------------
 1      1         1        1
 2      2         1        1
 3      1         2        1
 4      3         2        1

Now I want to have as a query result:

table1.id | table1.name | option | value
-------------------------------------
      1       "one"        1       1
      2       "two"        1       1
      3       "three"    
      1       "one"        2       1
      2       "two"    
      3       "three"      2       1

How do I achieve that?

I already tried:

SELECT
  table1.id,
  table1.name,
  table2.option,
  table2.value
FROM table1 AS table1
LEFT outer JOIN table2 AS table2 ON table1.id = table2.tbl1fk

but the result seems to omit the null vales:

1    "one"    1   1
2    "two"    1   1
1    "one"    2   1
3    "three"  2   1

SOLVED: thanks to Mahmoud Gamal: (plus the GROUP BY) Solved with this query

SELECT 
  t1.id,
  t1.name,
  t2.option,
  t2.value
FROM
(
  SELECT t1.id, t1.name, t2.option
  FROM table1 AS t1
  CROSS JOIN table2 AS t2
) AS t1
LEFT JOIN table2 AS t2  ON t1.id = t2.tbl1fk
                       AND t1.option = t2.option
group by t1.id, t1.name, t2.option, t2.value
ORDER BY t1.id, t1.name

Actually, your present solution *doesn't work*. Consider the extended test case in this [**SQLfiddle**](http://www.sqlfiddle.com/#!12/7f47a/3). — Erwin Brandstetter, Mar 26 '13 at 00:04

Mahmoud Gamal · Accepted Answer · 2013-03-25T13:04:56.827

8

You have to use CROSS JOIN to get every possible combination of name from the first table with the option from the second table. Then LEFT JOIN these combination with the second table. Something like:

SELECT 
  t1.id,
  t1.name,
  t2.option,
  t2.value
FROM
(
  SELECT t1.id, t1.name, t2.option
  FROM table1 AS t1
  CROSS JOIN table2 AS t2
) AS t1
LEFT JOIN table2 AS t2  ON t1.id = t2.tbl1_fk
                       AND t1.option = t2.option

SQL Fiddle Demo

edited Mar 25 '13 at 13:04

answered Mar 25 '13 at 12:53

Mahmoud Gamal

78,257
17
139
164

that works! only I have to add the GROUP BY Clause because the result is multiple! – kosta Mar 25 '13 at 13:08
Almost works, but not quite. See the SQLfiddle in my comment above. – Erwin Brandstetter Mar 27 '13 at 00:23
Seems expensive with cross-joins if the tables involved are large--no way around it? – ako Dec 30 '13 at 10:31
1

@ako - run the [`EXPLAIN` statement](http://dev.mysql.com/doc/refman/5.0/en/explain-output.html) on your query `EXPLAIN SELECT ...the rest of your query` and see if any optimizations can be done. Also ensure that indexes are set properly on your table. – Mahmoud Gamal Dec 30 '13 at 11:58
@MahmoudGamal But what was the reason in the first place the left outer join in the OP not return the nulls on the table two side--why is the cross-join necessary? – ako Dec 30 '13 at 20:05
1

@ako - `LEFT JOIN` works fine as expected, the point is that; in the sample data the user posted all the rows in the first tables have matching rows in the other table `table2`, so there are no `null` values because of that. But if you remove (for example) the last row from the `table2`, [**you will get a null values**](http://www.sqlfiddle.com/#!10/69759/1) , since the name `three` has no matching rows in the second table. But this is not what the OP wants, if you look at the desired result, he wants to select all the names from the `table1` with all possible combination from the `table2`. – Mahmoud Gamal Dec 31 '13 at 06:43
@ako - And this can't be done with `LEFT JOIN` that why I used `CROSS JOIN`. Yes, `LEFT JOIN` will give you `null` values for unmatched rows, but it won't give you all possible combination of the two tables, this what `CROSS JOIN` do. Also, note that I thought it is MySQL. but it isn't so the `EXPLAIN` won't work I think. – Mahmoud Gamal Dec 31 '13 at 06:45

Erwin Brandstetter · Answer 2 · 2013-03-25T23:57:27.487

Simple version: option = group

It's not specified in the Q, but it seems like option is supposed to define a group somehow. In this case, the query can simply be:

SELECT t1.id, t1.name, t2.option, t2.value
FROM  (SELECT generate_series(1, max(option)) AS option FROM table2) o
CROSS  JOIN table1 t1
LEFT   JOIN table2 t2 ON t2.option = o.option AND t2.tbl1_fk = t1.id
ORDER  BY o.option, t1.id;

Or, if options are not numbered in sequence, starting with 1:

...
FROM  (SELECT DISTINCT option FROM table2) o
...

Returns:

 id | name  | option | value
----+-------+--------+-------
  1 | one   |      1 |     1
  2 | two   |      1 |     1
  3 | three |        |
  1 | one   |      2 |     1
  2 | two   |        |
  3 | three |      2 |     1

Faster and cleaner, avoiding the big CROSS JOIN and the big GROUP BY.
You get distinct rows with a group number (grp) per set.
Requires Postgres 8.4+.

More complex: group indicated by sequence of rows

WITH t2 AS (
   SELECT *, count(step OR NULL) OVER (ORDER BY id) AS grp
   FROM (
      SELECT *, lag(tbl1_fk, 1, 2147483647) OVER (ORDER BY id) >= tbl1_fk AS step
      FROM table2
      ) x
   )
SELECT g.grp, t1.id, t1.name, t2.option, t2.value
FROM  (SELECT generate_series(1, max(grp)) AS grp FROM t2) g
CROSS  JOIN table1 t1
LEFT   JOIN        t2 ON t2.grp = g.grp AND t2.tbl1_fk = t1.id
ORDER  BY g.grp, t1.id;

Result:

 grp | id | name  | option | value
-----+----+-------+--------+-------
   1 |  1 | one   |      1 |     1
   1 |  2 | two   |      1 |     1
   1 |  3 | three |        |
   2 |  1 | one   |      2 |     1
   2 |  2 | two   |        |
   2 |  3 | three |      2 |     1

-> SQLfiddle for both.

How?

Explaining the complex version ...

Every set is started with a tbl1_fk <= the last one. I check for this with the window function lag(). To cover the corner case of the first row (no preceding row) I provide the biggest possible integer 2147483647 the default for lag().
With count() as aggregate window function I add the running count to each row, effectively forming the group number grp.
I could get a single instance for every group with:
```
(SELECT DISTINCT grp FROM t2) g
```
But it's faster to just get the maximum and employ the nifty generate_series() for the reduced CROSS JOIN.
This CROSS JOIN produces exactly the rows we need without any surplus. Avoids the need for a later GROUP BY.
LEFT JOIN t2 to that, using grp in addition to tbl1_fk to make it distinct.
Sort any way you like - which is possible now with a group number.

PSR · Answer 3 · 2013-03-25T13:00:04.773

0

try this

SELECT
  table1.id, table1.name, table2.option, table2.value  FROM table1 AS table11
JOIN table2 AS table2 ON table1.id = table2.tbl1_fk

edited Mar 25 '13 at 13:00

answered Mar 25 '13 at 12:54

PSR

39,804
41
111
151

That produces the following result: 1;"one";1;1 2;"two";1;1 1;"one";2;1 3;"three";2;1 but i want even the null values ! – kosta Mar 25 '13 at 12:59
same result even with JOIN, even with LEFT OUTER JOIN :( I'm using postgreSQL 9.1 – kosta Mar 25 '13 at 13:04

score -5 · Answer 4 · answered Nov 05 '13 at 09:42

-5

This is enough:

select * from table1 left join table2 on table1.id=table2.tbl1_fk ;

answered Nov 05 '13 at 09:42

user1389698

69
1

PostgreSQL LEFT OUTER JOIN query syntax

4 Answers4

SQL Fiddle Demo

Simple version: option = group

More complex: group indicated by sequence of rows

How?

Linked