我有一个不是我创建的存储过程。
在一些存储过程中,有一些嵌套的联接,我很难理解。
为了理解逻辑,如何更容易地编写它?为什么要使用这些?只是为了提高性能?
SELECT *
FROM
dbo.tblQuotes AS tblQuotes_1
LEFT OUTER JOIN
dbo.lstLines ON tblQuotes_1.LineGUID = dbo.lstLines.LineGUID
RIGHT OUTER JOIN
dbo.tblUsers
RIGHT OUTER JOIN
dbo.tblQuotes
RIGHT OUTER JOIN
dbo.tblClaims_Claim ON dbo.tblQuotes.ControlNo = dbo.tblClaims_Claim.ControlNo
RIGHT OUTER JOIN
dbo.tblNoteEntries
LEFT OUTER JOIN
dbo.tblNoteRecipients ON dbo.tblNoteEntries.ID = dbo.tblNoteRecipients.EntryGUID
INNER JOIN
dbo.tblNoteDiaries ON dbo.tblNoteEntries.ID = dbo.tblNoteDiaries.EntryGUID
INNER JOIN
dbo.tblNoteEntities ON dbo.tblNoteEntries.NoteGUID = dbo.tblNoteEntities.NoteGUID
INNER JOIN
dbo.tblNoteStore ON dbo.tblNoteEntries.NoteGUID = dbo.tblNoteStore.ID
AND dbo.tblNoteDiaries.NoteGUID = dbo.tblNoteStore.ID
AND dbo.tblNoteEntities.NoteGUID = dbo.tblNoteStore.ID
INNER JOIN
dbo.lstNoteTypes ON dbo.tblNoteStore.Type = dbo.lstNoteTypes.NoteTypeID
ON dbo.tblClaims_Claim.ClaimGuid = dbo.tblNoteEntities.AssociatedEntityGUID
ON dbo.tblUsers.UserGUID = dbo.tblNoteRecipients.UserGUID
ON tblQuotes_1.ControlGuid = dbo.tblNoteEntities.ControlGuid
LEFT OUTER JOIN
dbo.tblMaxQuoteIDs ON tblQuotes_1.QuoteID = dbo.tblMaxQuoteIDs.MaxQuoteID
LEFT OUTER JOIN
dbo.tblInsureds ON dbo.tblNoteEntities.AssociatedEntityGUID = dbo.tblInsureds.InsuredGUID
我通常将列表转换为缩进列表,就好像各种联接是嵌套的if语句一样。例如:
SELECT *
FROM dbo.tblQuotes AS tblQuotes_1
LEFT OUTER JOIN dbo.lstLines
ON tblQuotes_1.LineGUID = dbo.lstLines.LineGUID
RIGHT OUTER JOIN dbo.tblUsers
RIGHT OUTER JOIN dbo.tblQuotes
RIGHT OUTER JOIN dbo.tblClaims_Claim
ON dbo.tblQuotes.ControlNo = dbo.tblClaims_Claim.ControlNo
RIGHT OUTER JOIN dbo.tblNoteEntries
LEFT OUTER JOIN dbo.tblNoteRecipients
ON dbo.tblNoteEntries.ID = dbo.tblNoteRecipients.EntryGUID
INNER JOIN dbo.tblNoteDiaries
ON dbo.tblNoteEntries.ID = dbo.tblNoteDiaries.EntryGUID
INNER JOIN dbo.tblNoteEntities
ON dbo.tblNoteEntries.NoteGUID = dbo.tblNoteEntities.NoteGUID
INNER JOIN dbo.tblNoteStore
ON dbo.tblNoteEntries.NoteGUID = dbo.tblNoteStore.ID
AND dbo.tblNoteDiaries.NoteGUID = dbo.tblNoteStore.ID
AND dbo.tblNoteEntities.NoteGUID = dbo.tblNoteStore.ID
INNER JOIN dbo.lstNoteTypes
ON dbo.tblNoteStore.Type = dbo.lstNoteTypes.NoteTypeID
ON dbo.tblClaims_Claim.ClaimGuid = dbo.tblNoteEntities.AssociatedEntityGUID
ON dbo.tblUsers.UserGUID = dbo.tblNoteRecipients.UserGUID
ON tblQuotes_1.ControlGuid = dbo.tblNoteEntities.ControlGuid
LEFT OUTER JOIN dbo.tblMaxQuoteIDs
ON tblQuotes_1.QuoteID = dbo.tblMaxQuoteIDs.MaxQuoteID
LEFT OUTER JOIN dbo.tblInsureds
ON dbo.tblNoteEntities.AssociatedEntityGUID = dbo.tblInsureds.InsuredGUID
然后,如果可以在不破坏查询逻辑的情况下执行此操作,我会将任何不合适的ON条件移动到它们的正确位置。在这种情况下,如果没有一些重要的分析,我就无法做到这一点。
注意:我真的很讨厌这种类型的联接序列,如果你知道表和关系,你可能会把它重组成一个简单的联接列表,而不是奇怪的嵌套联接结构。但这取决于两件重要的事情:
- 这样做是为了优化查询的性能吗
即使是现在,在我使用过的最新版本的SQL Server(2014)中,也有一些查询可以通过在JOIN列表中移动表引用来优化。如果这就是列表被重新组织成这种嵌套结构的原因,那么撤消特殊组织可能会严重损害性能。当然,如果这是在SQL Server的早期版本中编写的,那么您可能需要解开这些连接,看看查询优化器现在是否能很好地处理这种结构。
- 这样做是因为数据规范化中的一些缺陷吗?这些缺陷实际上需要嵌套的JOIN列表才能工作
永远不要低估前任的愚蠢(例如,访问《每日WTF》,浏览几分钟。)如果数据结构的关联方式需要尴尬和非规范化的JOIN,那么它们就会以这样的方式关联。事实上,我还没有看到这个原因得到验证,但我内心深处知道,某些数据结构需要这样做。
如果可读性是您的目标,那么值得注意的是,您根本不需要嵌套或RIGHT JOIN。以下
- 在数学上是等价的
- 始终从左到右阅读(假设这是你喜欢的阅读方式)
- 没有嵌套
- 没有正确的JOIN
-
减少了不必要的字符和行
SELECT * FROM dbo.tblNoteStore nt_str JOIN dbo.lstNoteTypes nt_typ ON nt_str.[Type] = nt_typ.NoteTypeID JOIN dbo.tblNoteDiaries nt_dia ON nt_str.ID = nt_dia.NoteGUID JOIN dbo.tblNoteEntities entit ON nt_str.ID = entit.NoteGUID JOIN dbo.tblNoteEntries entri ON nt_str.ID = entri.NoteGUID and nt_dia.EntryGUID = entri.ID and entit.NoteGUID = entri.NoteGUID LEFT JOIN dbo.tblNoteRecipients recip ON entri.ID = recip.EntryGUID LEFT JOIN dbo.tblUsers usr ON recip.UserGUID = usr.UserGUID LEFT JOIN dbo.tblInsureds insur ON entit.AssociatedEntityGUID = insur.InsuredGUID LEFT JOIN dbo.tblClaims_Claim claim ON entit.AssociatedEntityGUID = claim.ClaimGUID LEFT JOIN dbo.tblQuotes qts_1 ON entit.ControlGUID = qts_1.ControlGUID LEFT JOIN dbo.lstLines lines ON qts_1.LineGUID = lines.LineGUID LEFT JOIN dbo.tblMaxQuoteIDs max_qt ON qts_1.QuoteID = max_qt.MaxQuoteId LEFT JOIN dbo.tblQuotes qts_2 ON claim.ControlNo = qts_2.ControlNo ;