The INNER JOIN to itself with a count turns out to have a lower cost query plan than the INTERSECT approach. On the ROW approach, it also seems to take longer than the simple EXISTS query. But I suppose I can put both of those into CTEs for convenience. I guess I was just hoping there was a lower cost approach than what I was already doing.
On Thu, Dec 18, 2014 at 2:07 PM, David Johnston <david.g.johnston@xxxxxxxxx> wrote:
Is the intersect any better than what I originally showed? On the ROW approach, I'm not sure where the context for that is coming from since it may not be in the intersection. Consider n1 and n2 are NOT friends but they have >0 mutual friends between them.The INTERSECT is a lot more direct about finding mutual friends. The ROW() = ROW() piece is independent of the mutual friends question - it should be put in a WHERE clause and you can test whether a row is returned which, if one is, means the two people are friends."One Query" does not mean you need to do everything all-at-once. I suggest you make use of CTEs (WITH) subqueries for each distinct calculation you need then join all of the CTE items together in a final query the outputs the data in the format desired.David J.
