"Original" SQL: Use caution with "number field 1-Number field 2" SQL (10-null = null) mysql

If there is only one table in the case

Table A:

ID num1,num2

1 10 5

2 10 0

3 20 0

Select ID, num1,num2,num1-num2 as subnum from A;

In this case:

The results are as follows:

ID NUM1 num2 Subnum

1 10 5 5

2 10 0 10

3 20 0 20

In a single-table case, so that in SQL, using the minus sign "-" is no problem ( numeric fields, must not be null), and if there is a field null, then the result of the subtraction is also null, for example: 10-null = null

However, if there are more than 2 tables associated, even if the fields of each table are not NULL, but left associative, the records in the right table are null if they are not.

Table B:

ID num3

1 5

3 0

Select a.ID, b.ID, A.num1, b.num3, a.num1-num3 as Subnum from a A

Left join b b on a.id=b.id

Results:

ID ID num1 num3 subnum

1 1 10) 5 5

2 null null　　　

Above, originally I need Is id=2 time, Subnum = 10, the result is null.

At this time using the left connection, you can not use the minus, it will be processed inside the program.

"Original" SQL: Use caution with "number field 1-Number field 2" SQL (10-null = null) mysql