LEFT OUTER JOIN (gives extra rows) problem

Question

I have two tables which I want to join together using a left outer join. However, even though my left table contains only unique values, the right table satisfies the CONDITION more than once and as such, adds extra rows to the resultset.

Code to replicate problem:

declare @tb1 table (c1 int)
declare @tb2 table (c2 int)

INSERT INTO @tb1 VALUES (1)
INSERT INTO @tb1 VALUES (2)
INSERT INTO @tb1 VALUES (3)
INSERT INTO @tb1 VALUES (4)

INSERT INTO @tb2 VALUES (3)
INSERT INTO @tb2 VALUES (4)
INSERT INTO @tb2 VALUES (5)
INSERT INTO @tb2 VALUES (6)

select * from @tb1 left outer join @tb2 ON c1 = c2

INSERT INTO @tb2 VALUES (3)
INSERT INTO @tb2 VALUES (4)
INSERT INTO @tb2 VALUES (5)
INSERT INTO @tb2 VALUES (6)

select * from @tb1 left outer join @tb2 ON c1 = c2

As you can see the first SELECT returns 4 rows, where the second SELECT 6, although the left table remains unchanged.

How does one stay strict to the left table, and only use the right table to COMPLEMENT the rows from the left table?

HELP!

RESULTS:
c1          c2
----------- -----------
1           NULL
2           NULL
3           3
4           4

[DUPLICATE @tb2 records]

c1          c2
----------- -----------
1           NULL
2           NULL
3           3
3           3
4           4
4           4

Answer 1

Sorry, but your thinking is skewed.

Think about it this way: if you only want one single row from tb2 for each row in tb1, which one should the server choose? The fact is that from the definition of a join, every row in the right-hand-side table that matches the left-hand-side row is a match and must be included.

You'll have to ensure tbl2 has distinct values for c2 before the join. Murph's suggestion might do it, provided your SQL variant supports DISTINCT [column] (not all do).