How does this matrix work repeatedly in SQL?

Question

Full disclosure, I'm noob in SQL

For two sparse matrices A and B, defined as:

A (row_number, column_number, value) and B (row_number, column_number, value)

I do not understand how this query is a multiplication of two matrices:

SELECT A.row_number, B.column_number, SUM(A.value * B.value)
FROM A, B
WHERE A.column_number = B.row_number
GROUP BY A.row_number, B.column_number

My confusion lies in the SUM syntax and GROUP BY / SELECT syntax

So for my GROUP BY / SELECT confusion I don't understand why the expressions

A.row_number and B.column_number required after SELECT statement

Why should we indicate that when we are already using SELECT and WHERE? For me it is like saying that we want to use SELECT with these expressions (A.row_number and B.column_number), although we have already returned the table from WHERE. Doesn't it make sense to just say SELECT *? I assume GROUP BY just requires you to type the expressions that it uses in the SELECT statement, but I don't know for sure.

For SUM, I just want to clarify, SUM only uses the value A.value and B.value from what returns true WHERE? Otherwise, you will multiply all A.value with all B.value.

Clarifying any of these would be very helpful. Thank!

Answer 1

create table A
(   column_number int,
    row_number int,
    value int
);
create table B
(   column_number int,
    row_number int,
    value int
);

insert A (column_number,row_number,value) values (1,1,1),(1,2,2),(2,1,3),(2,2,4);
insert B (column_number,row_number,value) values (1,1,10),(1,2,20),(2,1,30),(2,2,40);

( ) :

SELECT A.row_number as Ar, B.column_number as Bc,
A.value as Av,B.value as Bv,A.value*B.value as product
FROM A, B
WHERE A.column_number = B.row_number

+------+------+------+------+---------+
| Ar   | Bc   | Av   | Bv   | product |
+------+------+------+------+---------+
|    1 |    1 |    1 |   10 |      10 |
|    2 |    1 |    2 |   10 |      20 |
|    1 |    1 |    3 |   20 |      60 |
|    2 |    1 |    4 |   20 |      80 |
|    1 |    2 |    1 |   30 |      30 |
|    2 |    2 |    2 |   30 |      60 |
|    1 |    2 |    3 |   40 |     120 |
|    2 |    2 |    4 |   40 |     160 |
+------+------+------+------+---------+

, :

SELECT A.row_number, B.column_number,sum(A.value * B.value) as theSum
FROM A, B
WHERE A.column_number = B.row_number
GROUP BY A.row_number, B.column_number
+------------+---------------+--------+
| row_number | column_number | theSum |
+------------+---------------+--------+
|          1 |             1 |     70 |
|          1 |             2 |    150 |
|          2 |             1 |    100 |
|          2 |             2 |    220 |
+------------+---------------+--------+

Answer 2

• SELECT , . , .

• GROUP BY . .

Answer 3

A (n, m) B (m, p) C (n, p).

SQL , A B, :

• row_number
• column_number

(, ).

group by.

WHEREindependent of SELECT. First, responsible for obtaining the correct records, the second to get the correct columns.