What are the differences between sql commands?

Question

What are the differences between sql commands?

Navigation

#1 by (4 votes)

0

What's the difference, when should I use the following sql commands and which expressions can accompany these commands?

order by ,group by, join e union

What is the use and when should I use the commands below in a select?

1) where nomedaColuna in

2) uso de uma subquery

mysql sql query

asked by anonymous 30.09.2014 / 12:02

1 answer

Unknown column error in MySQL query How to get permission to write to the Windows registry via VB the key HKEY_LOCAL_MACHINE

score 4 · Accepted Answer

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'll 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

Let's go

Order By vs Group By

Order By

The Order By is used to sort the result of a query, for example:

SELECT
  funcionarioId,
  nome,
  departamento
FROM
  funcionarios
ORDER BY
  departamento;

This query would have 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

Notice that the department column was used for sorting, and that it was in alphabetical order.

The default sort is ASC (ascending) but we can also use the reverse 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 GROUP BY clause is used to group data when using 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 ORDER BY

SELECT
  departamento,
  COUNT(pessoaId) as mebros
FROM
  pessoas
GROUP BY
  departamento
ORDER BY
  departamento;

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, so we use the JOIN / p>

Join must 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 this great answer Bacco >

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

UNION is used when it is allowed to join (as its name says) the result of two queries.

In our case, if we wanted to raise in a query, all the people (clients and employees) of the database.

In order to use UNION, the queries must have the same number of columns, and the data types of the columns used must be compatible.

So, 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 I finish with the other commands