Join an empty table to return all rows

Question

I have a table (Table 1) that has a composite primary key (Column1 + Column2). I use it as a foreign key in another table (Table 2).

Now I want the SELECT statement to select all the records from Table 1 and Table2. But it returns me 0 rows, because table2 is empty. I want all the entries from table 1, and if it does not exist in table2, the value of the columns in table2 should be zero.

I know, I only need to join him. But I do not understand.

thanks

Answer 1

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

FK is your foreign key in the second table. Left Join returns all rows from table1, even if they do not exist in table2.

Answer 2

You need an external connection

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

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

Answer 3

You will need LEFT JOIN

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

Try it.

Answer 4

Use LEFT JOIN to join tables. See SQL SERVER JOINS for an understanding of the concept.