-1

Are the two SQL statements equivalent?

  1. select * from tb1 {full, left, right} join tb2 on true
  2. select * from tb1 inner join tb2 on true

In my opinion, I think these two statements are equivalent. But in PostgreSQL, it does not do the transformation. I do not know what other Databases do. Or is there any case I didn't consider?

kainwen
  • 356
  • 1
  • 12
  • according to the algorithm mentioned in [this](https://stackoverflow.com/a/40486913/2803565) answer, all of that joins must be equal when the condition is true – S.Serpooshan Nov 13 '18 at 06:43
  • 1
    what you mean by "in PostgreSQL, it does not do the transformation"? – S.Serpooshan Nov 13 '18 at 06:44
  • `NATURAL JOIN behaves like JOIN ... ON TRUE, producing a cross-product join`. So it will be cross join maybe.. When i read [Documentation](https://www.postgresql.org/docs/current/queries-table-expressions.html) – dwir182 Nov 13 '18 at 06:51
  • @S.Serpooshan If they are equal, in the RBO stage of postgres, it should be rewritten as inner join. But postgres does not do the rewrite. – kainwen Nov 13 '18 at 08:13
  • what i mean by equal, is that the algorithm they follow to find the results will produce same results, not a physical replacement – S.Serpooshan Nov 13 '18 at 09:20
  • `natural join` does **not** behave like a `JOIN ON TRUE` it behaves like a `JOIN` with all columns having the same name as part of the join condition. –  Nov 13 '18 at 12:32
  • Hi. Learn what LEFT/RIGHT JOIN returns: INNER JOIN rows plus unmatched left/right table rows extended by NULLs. FULL JOIN returns INNER JOIN rows UNION ALL unmatched left & right table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. Inner join on true is cross join so outer join adds null-extended rows not matched in a cross join. When just one table is emtpy there are no matches in either table, but there are rows in the other--unmatched. – philipxy Nov 13 '18 at 13:04
  • Why are you talking about rewrites? Examples can only show what happened, not what happens. How do the definitions from a DBMS manual or published textbook or reference differ for the inner vs outers? See [ask] re research. – philipxy Nov 13 '18 at 13:21
  • @S.Serpooshan Your own algorithm at that link is poorly written: Eg its first step of three is for "each row" but that should apply to all the steps that follow. Eg its last bullet should be a separate 4th step. After such editing, it correctly does *not* always give the same result for the two queries. So you might want to find the error in your justfication for claiming here that it does. – philipxy Nov 13 '18 at 13:37

1 Answers1

0

These constructs are not the same:

select *
from tbl1 inner join
     tbl2
     on true;

and:

select *
from tbl1 left join
     tbl2
     on true;

Under most circumstances, both perform a CROSS JOIN. BUT, there is a difference when tbl2 has no rows.

When tbl2 has no rows, the INNER JOIN version returns no rows. The LEFT JOIN version returns rows from the first table with NULL values from the second.

RIGHT JOIN and FULL JOIN have analogous behavior with empty tables.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786