# SQL cross join and multiple joins

Is there a way to improve this SQL query? It involves multiple cross join and joins.

I have 3 tables, and I want to compute the cardinal product:

table N:
N
-
4

table sums:
i   S
-----------
1   22
2   26
3   22

table mults:
i   j   M
-------------------
1   1   122
1   2   144
1   3   119
2   1   144
2   2   170
2   3   141
3   1   119
3   2   141
3   3   126


Here is a fiddle.

SELECT  ((N.n*M1.m)-(S1.s*S2.s)) / (SQRT((N.n*M2.m)-(pow(S1.s,2))) * SQRT((N.n*M3.m)-(pow(S2.s,2))))
FROM N
CROSS JOIN sums as S1
CROSS JOIN sums as S2
JOIN mults as M1
ON M1.i = S1.i
AND M1.j = S2.i
JOIN mults as M2
ON M2.i = S1.i
AND M2.j = S1.i
JOIN mults as M3
ON M3.i = S2.i
AND M3.j = S2.i;

-
Does it have to be done in SQL? I would suck the data out and do it in memory instead. There are some powerful matrix and numeric libraries out there. –  Leonid Nov 17 '12 at 17:33
yeah, I know LAPACK for example but with sql queries? –  cMinor Nov 17 '12 at 17:33
I agreed with @Leonid. Using a database like this is likely to fall apart if you try to apply this approach on a little larger data sets, but another issue is that it looks so much like a math problem that you really should use a math tool just to make it easier to maintain and debug later. –  Michael Zedeler Nov 18 '12 at 21:24