What are the differences between sql commands?

Asked

Viewed 329 times

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
mysql sql query

  • There is no way to answer which is faster, you have to test with real data in specific situations and check.

  • because people can barely see the question and negative?.. I’ve already taken -4

  • 1

    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.

  • 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..

  • 2

    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.

  • @Mustache did help me.. in parts I asked this question more to help people looking for topics related to sql commands than to me...

  • 2

    It is an extremely basic question, but no one was born knowing her answer, so it is pertinent. + 1

  • @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.

  • @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.

  • 3

    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.

Show 5 more comments

1 answer

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 vs Group By

Order By

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

Group By

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

Join vs Union

Join

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

UNION

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.