SQL two field subtraction statements

SQL two field subtraction statements
This article searches for a large number of SQL two field subtraction statements and function codes from the network, illustrating two field subtraction practices.

Select a. field 1, Field 2=a. Field 2-isnull ((select field 2 from Table 2 where a. Field 1 = Field 1), 0) from table 1 a method two

CREATE TABLE Table 1 (Field 1 varchar (50), field 2 int)
Insert INTO Table 1
Select ' 111001 ', UNION ALL
Select ' 111002 ', 9 UNION ALL
Select ' 111003 ', UNION ALL
Select ' 111004 ', 23

CREATE TABLE Table 2 (Field 1 varchar (50), field 2 int)
INSERT INTO Table 2
Select ' 111001 ', 3 UNION ALL
Select ' 111002 ', 2 UNION ALL
Select ' 111003 ', 12

Select a. Field 1, (a.[Field 2]-(IsNull (b.[field 2],0)) As Field 2 from table 1 a LEFT JOIN table 2 b
On a.[field 1] = b.[Field 1]

Result: Field 1 Field 2
111001 7
111002 7
111003 0 (not shown)
111004 23

Method Three

There are two identical tables T1 and T2, all with field a (character), B (numeric), C (numeric), as follows:
T1 T2
A b c a B C
A1 A2 2 10
A2 A3 3 15
A3 10 20
A4 10 20

Select T1.a as A, (T1.b-isnull (t2.b,0)) as B, (T1.c-isnull (t2.c,0)) as C from [T1] left join [T2] on (t1.a = t2.a)