3.18 Adding and Subtracting Matrices

3.18.1 Problem

You want to add or subtract matrices in the table.

3.18.2 Solution

To add matrices A and B, use:

SELECT DISTINCT m1.X, m2.Y, m1.Value+m2.Value Value
FROM Matrices m1, Matrices m2
WHERE m1.Matrix='A' AND m2.Matrix='B'
   AND m1.X=m2.X AND m1.Y=m2.Y

X           Y           Value       
----------- ----------- ----------- 
1           1           12
1           2           6
2           1           9
2           2           9

To subtract matrix B from matrix A, use this query, but replace the plus sign with a minus sign. The results of A-B are:

x           y           Value       
----------- ----------- ----------- 
1           1           0
1           2           0
2           1           -1
2           2           5

3.18.3 Discussion

This code follows the definitions of matrix addition and subtraction from algebra. To add two matrices, they must be of the same dimension (i.e., they must be equal), and then you just add elements on the same coordinates. Subtraction works the same way, except that you subtract element values rather than add.

The trick to this recipe's solution is in matching the elements on the same coordinates from the two matrices. We assume that the matrices are already of the same dimension; in other words, we assume they are equal. Then, we create two instances of the Matrices table (m1 and m2). We restrict m1 in the WHERE clause so that it represents matrix A, and we restrict m2 so that it represents matrix B. The elements of each matrix are now matched, and the plus or minus operator in the SELECT clause calculates the sum or difference.