当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - SQL STATEMENT QUERY

问题描述:

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?

网友答案:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: