Joining empty table to return all rows

Question

I have a table (Table1) which has a composite primary key(Column1 + Column2). I am using it as a foreign key in another table (Table2).

Now I want to a SELECT statement to select all records from Table1 and Table2. But its returning me 0 rows, because table2 is Empty. I want all records from table1 and if it does not exist in table2, value of Columns in Table2 should be null.

I know, I only need to Join it. But I am not getting it right.

Thanks

Solution 1

SELECT * FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.Id = T2.FK

FK is your foreign key on the second table. A Left Join will return all rows from table1 even if they don't exist in table2.

Solution 2

You need an outer join

SELECT *
FROM   table1
       LEFT OUTER JOIN table2
         ON table1.column1 = table2.column1
            AND table1.column2 = table2.column2  

Left means preserve all rows from the left (first) table in the query.

Solution 3

You need a LEFT JOIN

SELECT Table1.*, Table2.*
FROM Table1
LEFT JOIN Table2 ON Table1.Column1 = Table2.Column2

Try that out.