Join two tables with different number of rows (in sql server)

Question

I have two tables: A (col1, col2, col3, COL4)

B (col1, col2, col3, COL4)

Table A has 4 entries (rows) and B has 6 rows. I want to join them as follows, for example, join them in a C table

C ( B.col1 B.col2 , A.col3, A.col4, B.col3, B.col4) (the tables have different values ​​in the records, only col1 and col2 contain the same values)

when I join them on A.col1 = B.col1 and A.col2 = B.col2 I accept the Cartesian product: (

PS I want to have 6 lines in C , where B.col1, B.col2, B.col3, B.col4 have 6rows and A.col3, A.col4 have 4 lines and the other 2 null

Please help me..

Answer 1

You need to use FULL OUTER JOIN

Select a.Col1,
       a.Col2,
       a.Col3,
       a.Col4,
       b.Col3,
       b.Col4

From TableA a
Full Outer Join TableB b on a.Col1 = b.Col1
                         And a.Col2 = b.Col2

EDIT:

"does not work" will not help anyone. Perhaps you could provide more details on why it does not work for you.

You can add some sample data and the actual expected result to your question.

eg

Create Table #TableA
(
Col1 
...
)

Insert Into #TableA
Values (...)

The clearer your question, the better the answers. If people do not fully understand what your exact problem and expected performance is, then how should we provide you with a complete and correct answer.

Answer 2

Try

SELECT A.col1, A.col2, A.col3, A.col4, B.col3, B.col4
FROM A
FULL OUTER JOIN B on (A.col1 = B.col1 AND A.col2 = B.col2)

Answer 3

You can use the left outer join there. But I have an additional question on how to fill empty strings with some other values ​​than NULL ..