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.johns...@gmail.com> wrote: > > On Thu, Dec 18, 2014 at 3:02 PM, Robert DiFalco <robert.difa...@gmail.com> > 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. > >
