What is the use of 'SELECT 1'?

Asked

Viewed 5,074 times

9

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

1.14 - Designing the CPF and the amount to pay in rentals by customers who have rented media and have not made any payment yet.

inserir a descrição da imagem aqui

The consultation carried out 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;

Researching how to use NOT EXISTS, I found that in several examples they used the SELECT 1. I would like to know how it works and where I should use it.

mysql sql

2 answers

5


I believe it’s for "nay" bring the results pro "return", as in the case is a sub-query then you wouldn’t need to set any column, but still you need to write something.

We usually do this when we count records:

SELECT COUNT(1) FROM tabela

If you have such a table:

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

And run 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" get a better performance.

  • I liked the answer. Although doing a quick search here, I found that some tests performed using SELECT 1 and SELECT * in Queries, did not give much difference in performance.But it answered well what I wanted to know, thank you!

  • 1

    @williamhk2 for this I wrote the "try to", This can only be felt when you have a very high number of records. However today most bank engines have optimization techniques :)

  • good William, thank you!

  • Basically it will return the existing values, as if it were "SELECT true FROM table", it is right work with booleano in a query, and is not recommended.

  • Sorry, I do not know if it is recommended or not @Edilson, could quote some source, just out of curiosity. Thank you

  • Because basically it will select everything, which has any value. So it has its own cases to use. Although not an example related to the current question, imagine, a table usuarios with a field usuario property unique, and run a query, update usuarios set usuario = 'teste' where true. For this consultation will fail, because the field usuario has a unique reference, but for a select query, it would return true for any and all values in it.

  • @Edilson you’re reversing logic, I’m sorry, but the case here is not with WHERE rather with SELECT. I think they are two very different situations and the question here talks about 1 in SELECT and not in WHERE. I believe your comment would only be an addition to the use of 1 but is not linked to the situation :)

  • Okay, but it’s well known that one thing leads to another, that’s one of the reasons I made reference to it. Although I have not invented any logic, it is true that they are different situations, although I have already mentioned this also in the other comment.

  • @Edilson I was referring to the logic of the question and not the code :)

  • It is also widely used in a EXISTS. Example: WHERE
 EXISTS( SELECT 
 1
 FROM
 customers
 WHERE id > 1). Just commented above, performance.

Show 5 more comments

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

That’ll bring up some number of records (EXISTS) if there is any place where these conditions of the WHERE are satisfied and no record (NOT EXISTS) if there is no place where they are satisfied.

The only relevant information at the point is whether Mysql found anything or not. The exact content of the tuples found is irrelevant. However, the clause SELECT should bring at least one column with information. Given that none of the columns is relevant, you could choose to put any sets of columns in that SELECT, but the most interesting column set would be one that would force Mysql to look at as few columns as possible when assembling the result. This minimum possible number of columns is zero, and therefore the SELECT should not have any of the result columns. However, like SELECT need to have something, the solution is to put the SELECT 1, that brings a simple constant and negligible weight in the result.

  • 1

    It took me a while to understand, but I liked the answer. Although doing a quick search here, I found that performed some tests using SELECT 1 and SELECT * in Queries, not much difference in performance. But you answered well what I wanted to know, thank you!

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.