sqlsql-serversql-server-2005t-sql

Joining empty table to return all rows

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 
  • 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.