SQL STATEMENT QUERY

Question

I have table1Name with data populated and table2 with no data populated.

select * from [database1Name].dbo.table1Name 
join [database1Name].dbo.table2Name 
on [database1Name].dbo.table1Name.fieldName like value;

After running the above sql statement it joins the tables but does not show any populated data from the table 'table1Name'.

Why does this happen?

Answer 1

Using JOIN which is an INNER JOIN means that it will get you only data where the condition matches. So if the second table has not data, then the condition is never met, so you get no data in return.

In your case you need a LEFT JOIN. This will get all the rows from the left table (table1Name in your case) and the corresponding values from the right table when the condition is met.

SELECT * 
FROM   [database1Name].dbo.table1Name 
       LEFT JOIN [database1Name].dbo.table2Name 
           ON [database1Name].dbo.table1Name.fieldName like [database1Name].dbo.table2Name.fieldName;

Just to mention that using joins mean that you might get multiple times a single row from a specific table. For instance since you have a LIKE condition, if fieldName of Table 1 matches fieldName in 2 rows from Table 2 then you will get two rows containing the same row from Table 1 and the two rows from Table 2:

Example:

Table1

FieldName 
1
2

Table2

FieldName OtherField
1         1
1         2

Result of LEFT JOIN

T1FieldName T2FieldName T2OtherField
1           1           1
1           1           2
2           NULL        NULL