What is the use of 'SELECT 1'?

Question

What is the use of 'SELECT 1'?

Navigation

#1 by (4 votes)
#2 by (2 votes)

6

I am making a query using NOT EXISTS in a college job. The situation is as follows:

1.14 - Design the CPF and the amount payable in rents for customers who have rented media and have not yet made any payment.

The query performed and that worked, follows below:

SELECT A.CPF_Cliente, 
       SUM(ValorPagar) AS TotalValorPagar
FROM   Aluguel A 
WHERE NOT EXISTS (SELECT 1
                   FROM  Pagamentos B 
                   WHERE  A.CPF_Cliente = B.CPF_Cliente 
                          AND A.ID_Midia = B.ID_Midia 
                          AND A.DataLocacao = B.DataLocacao) 
GROUP  BY A.CPF_Cliente;

Searching on how to use NOT EXISTS , I noticed that in many examples they used SELECT 1 . I would like to know how it works and where I should use it.

mysql sql

asked by anonymous 02.11.2015 / 00:10

2 answers

2

Let's see this part of SQL:

               FROM  Pagamentos B 
               WHERE  A.CPF_Cliente = B.CPF_Cliente 
                      AND A.ID_Midia = B.ID_Midia 
                      AND A.DataLocacao = B.DataLocacao

This will then bring some number of records ( EXISTS ) if there is any place where those WHERE conditions are met and no register ( NOT EXISTS ) if there is no place where they are satisfied. >

The only relevant information at the point is whether MySQL has found anything or not. The exact content of the tuples found is irrelevant. However, the SELECT clause should bring at least one column with information. Since none of the columns is relevant, you could choose to place any column sets in this SELECT , but the most interesting column set would be the one that forces MySQL to look at as few columns as possible when assembling the result. This least possible number of columns is zero, so% w / o% should not have any of the result columns. However, as SELECT needs to have something, the solution is to put SELECT , which brings a simple constant and negligible weight in the result.

02.11.2015 / 00:18

What is the difference between a DLL produced with C ++ and a C # How to get the battery percentage?

score 4 · Accepted Answer

I believe that it is for "no" to bring the results to "return" , as in case it is a sub-query then you do not I would need to define no columns, but you still have to write something.

We usually do this when we count records:

SELECT COUNT(1) FROM tabela

If you have a table like this:

 id | nome   |
  1 | João   |
  2 | Maria  |
  3 | Pedro  |

And running this SELECT nome FROM tabela returns:

João
Maria
Pedro

Returned a total of 14 characters

And this SELECT 1 FROM tabela returns:

1
1
1

Returned a total of 3 characters, I believe this is the main reason "try" to achieve better performance.