0
What’s the difference, when I should use the following sql commands and which expressions can follow these commands?
order by ,group by, join e union
What is it for and when to use the commands below in a select?
1) where nomedaColuna in
2) uso de uma subquery
4
There is a big difference between the commands you mentioned and there is no way to compare them. To help you understand each one, I will explain how they work.
I will use these same entities with the following data in all examples:
funcionarios
-----------------------------------------------
funcionarioId | nome | departamento
-----------------------------------------------
1 | Mauro Ramos | Financeiro
2 | Felipe Schimidt | Compras
3 | Luisa Maria | Marketing
4 | José Luiz | Marketing
5 | Valéria Viana | Financeiro
clientes
------------------------------------------
clienteId| nome | grupo
------------------------------------------
1 | Roberto Luiz | VIP
2 | Fabio Santos | Normal
3 | Mel Lisboa | Normal
4 | Ana Maria | VIP
5 | Lidiane Silva | VIP
vendas
--------------------------------------
vendaId | clienteId | valor
--------------------------------------
1 | 5 | 3.500,00
2 | 4 | 1.500,00
3 | 1 | 3.000,00
4 | 3 | 2.200,00
5 | 1 | 2.500,00
6 | 5 | 4.000,00
7 | 1 | 500,00
8 | 3 | 1.000,00
Come on
Order By is used to sort the result of a query, for example:
SELECT
funcionarioId,
nome,
departamento
FROM
funcionarios
ORDER BY
departamento;
Such consultation would bring the following result:
funcionarioId | nome | departamento
------------------------------------------
2 | Felipe Schimidt | Compras
5 | Valéria Viana | Financeiro
1 | Mauro Ramos | Financeiro
3 | Luisa Maria | Marketing
4 | José Luiz | Marketing
Note that the department column was used for ordering, and that it was in alphabetical order.
The default ordering is ASC (ascending) but we can also use the inverse order DESC (descending)
SELECT
funcionarioId,
nome,
departamento
FROM
funcionarios
ORDER BY
funcionarioId DESC;
And the result would be
funcionarioId | nome | departamento
------------------------------------------
5 | Valéria Viana | Financeiro
4 | José Luiz | Marketing
3 | Luisa Maria | Marketing
2 | Felipe Schimidt | Compras
1 | Mauro Ramos | Financeiro
The clause GROUP BY is used to group data when we use expressions in the query. These expressions can be, for example SUM, COUNT, AVG. For example
SELECT
departamento,
COUNT(funcionarioId) as membros
FROM
funcionarios
GROUP BY
departamento;
The result would be
departamento | membros
Financeiro | 2
Compras | 1
Marketing | 2
And it can still be used in combination with the ORDER BY
SELECT
departamento,
COUNT(pessoaId) as mebros
FROM
pessoas
GROUP BY
departamento
ORDER BY
departamento;
Whether it would be
departamento | membros
-----------------------
Compras | 1
Financeiro | 2
Marketing | 2
Imagine if we wanted to bring a listing of all sales with customer names, the name is in the customer table and sales table only knows the customer id, for this we use the JOIN
Join should be done by linking which fields identify the records between the tables.
There are some types of JOIN and you can see more about them in that excellent reply of Bacchus
SELECT
V.vendaId,
V.clienteId,
C.nome AS nomeCliente,
V.valor
FROM
vendas V
INNER JOIN cliente C on (V.clienteId = C.clienteId)
And the result would be
------------------------------------------------------
vendaId | clienteId | nomeCliente | valor
------------------------------------------------------
1 | 5 | Lidiane Silva | 3.500,00
2 | 4 | Ana Maria | 1.500,00
3 | 1 | Roberto Luiz | 3.000,00
4 | 3 | Mel Lisboa | 2.200,00
5 | 1 | Roberto Luiz | 2.500,00
6 | 5 | Lidiane Silva | 4.000,00
7 | 1 | Roberto Luiz | 500,00
8 | 3 | Mel Lisboa | 1.000,00
The UNION is used when merging (as the name says) the result of two queries.
In our case, if we wanted to raise in a query, all people (clients and employees) of the database.
To use UNION, queries must have the same number of columns, and the data types of the columns used must be compatible.
Then we would have:
SELECT
clienteId AS Id,
nome,
grupo as Agrupamento
FROM
clientes
UNION
SELECT
funcionarioId as Id,
nome,
departamento as Agrupamento
FROM
funcionarios
That would return the following result:
-----------------------------------------------
Id | nome | Agrupamento
-----------------------------------------------
1 | Mauro Ramos | Financeiro
2 | Felipe Schimidt | Compras
3 | Luisa Maria | Marketing
4 | José Luiz | Marketing
5 | Valéria Viana | Financeiro
1 | Roberto Luiz | VIP
2 | Fabio Santos | Normal
3 | Mel Lisboa | Normal
4 | Ana Maria | VIP
5 | Lidiane Silva | VIP
I have to stop to solve a problem here, then finish with the other commands
thanks for your reply.. even incomplete thank you for your help I hope the end of your reply^^
@Pedrorangel now only missing subselect and IN operator
ok grateful^^
Browser other questions tagged mysql sql query
You are not signed in. Login or sign up in order to post.
There is no way to answer which is faster, you have to test with real data in specific situations and check.
– Maniero
because people can barely see the question and negative?.. I’ve already taken -4
– Pedro Rangel
Yeah, I don’t understand why in this case, the question isn’t in the best shape, but it doesn’t have any serious problems. Once in a while someone negative without knowing why and others go after why someone did it before.
– Maniero
How should I proceed...I lost 8 points? I can’t close this question because I already have an answer and I also don’t want to harm who answered me..
– Pedro Rangel
I think there is no problem in leaving. The answer helped you? It seems to help other people. Maybe someone takes the negative out. Who knows how to get the most positive vote.
– Maniero
@Mustache did help me.. in parts I asked this question more to help people looking for topics related to sql commands than to me...
– Pedro Rangel
It is an extremely basic question, but no one was born knowing her answer, so it is pertinent. + 1
– Bacco
@Bacco thanks even... as I said there are many search forums on sql commands.. wanted to create one on this site for discussion and learning.
– Pedro Rangel
@Pedrorangel just take a little care not to overdo it, I can not say why they have negatively but I know that some people do not like very basic things, that find anywhere very easy. I’m not saying that they are absolutely right, but I understand that they don’t want to see a lot of question that exists in any tutorial.
– Maniero
Canonical question is always good, for doubts that are common to a wide range of users. The only recommendation is to read well the [Ask] and the Community FAQ to give that "fancy" in the question, without "misses the hand". I saw by your editions that you are starting to be more careful with the quality of the posts, this is good for everyone.
– Bacco