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

Lotuseater

Can you show your join condition ? – Purebred

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

Devolve

here we go, it was so simple... don't know how many times I have used joins before... :( Anyway its Monday.. :P .... thanks Much – Lotuseater

@Scorpion: You're welcome. And yeah, the Monday syndrome hits us all ;) Don't forget to close your question by selecting the right answerer. – Devolve

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

Mandragora

Answer 3

You need a LEFT JOIN

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

Try that out.

Pannier

You don't need to specify Table1.*, Table2.*; just using * will return all columns from both tables. – Devolve

Answer 4

Use LEFT JOIN for join you tables. See SQL SERVER JOINS to understand the concept.

Tutelary

Answer 5

Change the location of your table for example if t1 is empty data: select t1.a,t2.a from t1 left join t2 on t1.a=t2.a you change to: select t1.a,t2.a from t2 left join t1 on t1.a=t2.a

Morpheus