SQL – left join performs cartesian/multiplies matched rows

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.

Answer

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