What is a "0" field in the SQL SELECT?

Question

I didn’t understand the use of 0 in this code:

SELECT substring ... 0 AS PARCELADO, ...
FROM ...

What does he do?

Answer 1

The use of constants in SQL predicts that the result of the field is always equal to constant.

In this case,

0 AS parcelado

You are bringing all parceled fields as 0. Assuming there are 4 records

| CampoA  |  CampoB  |
|    1    |  José    |
|    2    |  João    |
|    3    |  Pedro   |

If I do

SELECT
  CampoA,
  CampoB,
  0 AS Parcelado
FROM
  Minha Tabela

I would have as a result

| CampoA  |  CampoB  | Parcelado |
|    1    |  José    |     0     |
|    2    |  João    |     0     |
|    3    |  Pedro   |     0     |

All right, I get it. But what for?

This can be used as a ruse to:

1. Fill this field in Runtime via code
2. Bring a field expected by legacy code
3. Meet a data model where this field would not be required
4. Have a field to be checked or unchecked on the interface

Answer 2

You didn’t put all the code in, so it might be something different than it looks.

Contrary to what many imagine SELECT can select any valid information in SQL. Then the 0 is valid information, is a numeric literal.

Valor dummy facilitating code creation at runtime

It is common to use 0 at the beginning or end when the query is being built by the application gradually. When you assemble the list of fields to be selected to control whether it should have a comma or not gets a little more complicated, then an extra field is simulated to ensure the linearization of the code.

It may be so (in pseudocode):

query = "SELECT 0"
query += ", " + campo1
query += ", " + campo2

Or else:

query = "SELECT "
query += campo1 + ", "
query += campo2 + ", "
query += "0"

This way the comma is not "loose".

Maintaining natural data order

It can also be used so that no specific order is used. One way for you to override some order used because of the other clauses is to use this to indicate that explicitly something that makes all elements equal for order comparison purposes:

ORDER BY (SELECT 0)

Help in understanding the query

When data comes from different sources you may want to know what part of query resulted that row, then uses a column only for identification:

SELECT 0, nome, ... FROM cliente WHERE id = 123
UNION ALL
SELECT 1, nome, ... FROM cliente WHERE id = 456

I put in the Github for future reference.

Virtual column

It is possible that your application needs a virtual column (sometimes called a calculated column). That is, although your table does not have that column, a query specific need of this column for some reason.

As stated before, the SELECT can take any expression and not just the table fields. These expressions can be useful for calculations or other manipulations that the application needs to process.

As in this case there is no calculation or variation of the data it is likely that this information will be filled in somehow by the application and will be used by it or will serve for some decision at the time the data returns to write in the database.

Caputo’s answer gives more details on this possibility.