如何使用join检索空值

本文介绍了如何使用join检索空值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我使用以下查询来检索值.. SELECT VoucherTypes.specification，VoucherTypes.types，VoucherHead.VoucherDate，VoucherHead.VoucherNo，Accounts.AccountNo，Accounts.AccountName， CostAccounts.CostAccountName，VoucherDetail.VoucherDetail，VoucherDetail.Debit，VoucherDetail.Credit，VoucherHead.VoucherID， Accounts.AccountID FROM VoucherHead INNER JOIN VoucherDetail ON VoucherHead.VoucherID = VoucherDetail.VoucherID INNER JOIN VoucherTypes ON VoucherHead.VoucherType = VoucherTypes.vtypeid INNER 加入 帐户 ON VoucherDetail.Accountid = Accounts.AccountID 已满 OUTER JOIN CostAccounts ON VoucherDetail.costaccount = CostAccounts.ID WHERE （VoucherTypes.types = @ vtypes 或 @ vtypes = ' 0'） AND （VoucherDetail.VoucherDetail LIKE @ voucherdetail OR @ voucherdetail = ' 0'） AND （VoucherDetail.Debit = @ debit 或 @ debit = 0 。 0001 ） AND （VoucherDetail.Credit = @信用 或 @ credit = 0 。 0001 ） AND （VoucherHead.VoucherDate BETWEEN @ date1 AND @ date2 ） AND （VoucherHead.Branchno = @ branchid ） AND （CostAccounts.CostAccountName LIKE @ coststr 或 CostAccounts.CostAccountNo LIKE @ coststr 或 VoucherDetail.costaccount IS NULL ） AND （Accounts.AccountName LIKE @ str OR Accounts.AccountNo LIKE @ str ） 但是这个查询没有显示voucherdetail.costaccount为空的任何行...请帮我解决这个问题... 解决方案 嗨Nijish a， 很明显，结果不会返回NULL值，因为SQL在使用等运算符（=）进行比较时不理解NULL值。 你可以在加入比较时尝试ISNULL吗？ 类似的东西 CostAccounts ON Isnull（VoucherDetail.costaccount， 0 ）= Isnull（CostAccounts.ID， 0 ） 请让我知道你是否有任何疑虑或疑问，或者我错过了什么。 谢谢 Advay Pandya I have used following query for retrieving values..SELECT VoucherTypes.specification, VoucherTypes.types, VoucherHead.VoucherDate, VoucherHead.VoucherNo, Accounts.AccountNo, Accounts.AccountName, CostAccounts.CostAccountName, VoucherDetail.VoucherDetail, VoucherDetail.Debit, VoucherDetail.Credit, VoucherHead.VoucherID, Accounts.AccountIDFROM VoucherHead INNER JOIN VoucherDetail ON VoucherHead.VoucherID = VoucherDetail.VoucherID INNER JOIN VoucherTypes ON VoucherHead.VoucherType = VoucherTypes.vtypeid INNER JOIN Accounts ON VoucherDetail.Accountid = Accounts.AccountID FULL OUTER JOIN CostAccounts ON VoucherDetail.costaccount = CostAccounts.IDWHERE (VoucherTypes.types = @vtypes OR @vtypes = '0') AND (VoucherDetail.VoucherDetail LIKE @voucherdetail OR @voucherdetail = '0') AND (VoucherDetail.Debit = @debit OR @debit = 0.0001) AND (VoucherDetail.Credit = @credit OR @credit = 0.0001) AND (VoucherHead.VoucherDate BETWEEN @date1 AND @date2) AND (VoucherHead.Branchno = @branchid) AND (CostAccounts.CostAccountName LIKE @coststr OR CostAccounts.CostAccountNo LIKE @coststr OR VoucherDetail.costaccount IS NULL) AND (Accounts.AccountName LIKE @str OR Accounts.AccountNo LIKE @str)But This query does not show any row in which voucherdetail.costaccount is null...pls help me to solve this... 解决方案 Hi Nijisha,It's obvious that the result will not return with NULL values because SQL does not understand NULL value while comparing using equal operator (=).Can you please try ISNULL while comparing in join ?Something LikeCostAccounts ON Isnull(VoucherDetail.costaccount,0) = Isnull(CostAccounts.ID,0)Please let me know if you have any concern or query or if I am missing something.ThanksAdvay Pandya 这篇关于如何使用join检索空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！