Compare data from two SQL Server tables

Question

Good night,

I am studying SQL queries and I came across a problem in a query, I have two tables:

Table: Products

+----------------+--------------+--------+
| Codigo         | Produto      | Valor  |
+----------------+--------------+--------+
| 01             | Pipoca       | 5.00   |
| 02             | Refrigerante | 4.50   |
| 03             | Chocolate    | 6.00   |
| 04             | Amendoas     | 18.90  |
| 05             | Macarrão     | 3.69   |
+----------------+--------------+--------+ 

Table: Orders

+----------------+--------------+--------+
| Codigo         | Produto      | Valor  |
+----------------+--------------+--------+
| 01             | Pipoca       | 2.90   |
| 02             | Refrigerante | 4.70   |
| 03             | Chocolate    | 5.80   |
| 04             | Amendoas     | 17.90  |
| 05             | Macarrão     | 4.05   |
| 06             | Batata       | 7.00   |
+----------------+--------------+--------+ 

I need to make a query that returns to me the following table:

+----------------+--------------+------------+
| Codigo         | Produto      | Diferenca  |
+----------------+--------------+------------+
| 01             | Pipoca       |  2.10      |
| 02             | Refrigerante | -0.20      |
| 03             | Chocolate    |  0.20      |
| 04             | Amendoas     |  1.00      |
| 05             | Macarrão     |  0.36      |
| 06             | Batata       |  7.00      |
+----------------+--------------+------------+ 

Can someone please help me ?

Answer 1

Answering your scenario you can do something like this

   select 
    ISNULL(A.codigo,B.codigo), 
    ISNULL(A.produto, B.produto), 
    (ISNULL(B.valor,0) - ISNULL(A.valor,0)) as Valor 
from produtos A  with (nolock) right join pedidos B with (nolock)
ON A.codigo=B.codigo

I also added the WITH (NOLOCK) parameter that you should think about using. The SELECT command allows the use of this NOLOCK option, thus avoiding locks with INSERT commands.