-1

I have a very simple example but can't understand why final result of LEFT JOIN returns 4 rows with value 3.

CREATE or REPLACE TABLE t1 (col1 INTEGER);
CREATE or REPLACE TABLE t2 (col1 INTEGER);

INSERT INTO t1 (col1) VALUES 
   (1),
   (2),
   (3),
   (3);

INSERT INTO t2 (col1) VALUES 
   (3),
   (3);

SELECT t1.col1, t2.col1 FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col1 ORDER BY 1,2

COL1|COL1
1   |NULL
2   |NULL
3   |3
3   |3
3   |3
3   |3

Do I understand correctly that LEFT returns as many results for each row in left table as many matches it finds in the right table? (nb of rows times x nb of rows in right table). If yes, why such behaviour? It seems that after LEFT a CARTESIAN is performed.

marcin2x4
  • 1,321
  • 2
  • 18
  • 44
  • 1
    Both Outer & Inner Join return the number of matching rows from the 1st table times the number of rows from the 2nd table. If it's a PK/FK-join it's 1*n, otherwise m*n. See https://stackoverflow.com/a/27458534/2527905 – dnoeth Oct 28 '20 at 21:39
  • A table without a primary key is meaningless. Duplicates add no meaning. What does it *mean* that value `3` is present twice in both tables? – wildplasser Oct 28 '20 at 21:42
  • It's an example to understand the behaviour. – marcin2x4 Oct 28 '20 at 21:44

1 Answers1

2

You are seeing these results because you have duplicate values in the columns you are joining on.

Each '3' in t1 will join to each '3' in t2 so you get 2 rows in your resultset for every '3' in t1

NickW
  • 8,430
  • 2
  • 6
  • 19