SQL优化问题

Question

我有两张桌子。一个表包含有关资产的信息，另一个表包含它们之间的关系。如何优化当前查询并获得相似的结果。

SELECT a1.ID FROM Asset a1
WHERE a1.AssetId =
(SELECT r.DestinationAssetId
FROM Relation r
INNER JOIN Asset a2 ON a2.AssetId = r.SourceAssetId
WHERE a2.ID = '1112174' and r.RelationshipType = 'Video File')

结果: 13412331 (与a2.ID =‘1125574’相关的资产ID)

就我个人而言，我不喜欢这个愚蠢的子查询，有没有什么方法可以避免它并优化这个查询。

谢谢!

Answer 1

您可以丢失子查询：

SELECT dest.ID 
FROM 
Asset src
JOIN Relation r ON src.AssetId = r.SourceAssetId
JOIN Asset dest ON dest.AssetID = r.DestinationAssetID
WHERE src.ID = '1112174' and r.RelationshipType = 'Video File'

这并不是性能上的优化，但它更简洁一些。

Answer 2

乍一看，这看起来会做同样的事情：

SELECT a1.ID
FROM Relation r
INNER JOIN Asset a2 ON a2.AssetId = r.SourceAssetId
INNER JOIN Asset a1 ON a1.AssetId = r.DestinationAssetId
WHERE a2.ID = '1112174' and r.RelationshipType = 'Video File'

Answer 3

试试这个：

SELECT a1.ID
FROM Asset a1
INNER JOIN Relation r ON a1.AssetId = r.DestinationAssetId
INNER JOIN Asset a2 ON a2.AssetId = r.SourceAssetId
WHERE a2.ID = '1112174' and r.RelationshipType = 'Video File'