I was doing some queries, and there was a need to perform grouping of a table that was in a JOIN within the query. For example.
Sales table.
+----+---------+------------+----------+
| Id | Cliente | Data | Vendedor |
+----+---------+------------+----------+
| 1 | 123 | 2018-03-20 | 12 |
| 2 | 456 | 2018-03-20 | 34 |
+----+---------+------------+----------+
Key (Id)
Sales Items:
+-------+---------+------------+----------------+
| Venda | Produto | Quantidade | Valor_Unitario |
+-------+---------+------------+----------------+
| 1 | 123 | 3 | 5,50 |
| 1 | 456 | 9 | 5 |
| 2 | 789 | 5 | 7,0 |
| 2 | 101 | 7 | 7,0 |
+-------+---------+------------+----------------+
Key (Sale, Product)
The query I was writing was a simple JOIN.
SELECT * FROM vendas v INNER JOIN vendas_itens vi ON vi.Venda = v.Id
But at a certain point, you wanted to know how much each sale and order information is worth. Determined to have such information, I wrote the two queries below.
SELECT V.*,
SUM(Quantidade * Valor_Unitario) AS [Total]
FROM vendas v
LEFT JOIN vendas_itens vi
ON vi.Venda = v.Id
GROUP BY V.Id,
V.Cliente,
V.Data,
V.Vendedor
E
SELECT V.*,
(SELECT SUM(Quantidade * Valor_Unitario)
FROM vendas_itens vi
WHERE vi.Venda = v.Id ) AS [Total]
FROM vendas v
I checked the execution plan for both queries, but there was only one difference.
Iwouldliketoknowthefollowing:
-Whatistheperformancedifferencebetweenthetwoqueries?Isthereanyadvantagebetweenoneandtheother(takingthefactofthesecondwriteless)?-(I'mstilljunior)Whichoneismore"professional"? Or was it just a matter of taste?
I did some testing on a table with 15k records and saw no difference in performance.
Any improvement on the question, just comment, please.
EDIT1: As Joseph well remembered, the first query should be LEFT since the subquery will not limit the scope of the first table. And as he asked, there are no indexes in the tables, just the keys.