What is a "0" field in SQL SELECT?

5

I did not understand the use of 0 in this code:

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

What does it do?

    
asked by anonymous 08.12.2014 / 17:48

2 answers

9

You did not put the whole code, so it might be something different than it looks.

Contrary to popular belief, SELECT can select any valid information in SQL. Then 0 is valid information, it's a numeric literal.

Dummy value making it easy to create code at runtime

It is common to use 0 at the beginning or the 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 it does not get a little more complicated, then an extra field is simulated to ensure code linearization.

It can be this way (in pseudocode):

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

Or also:

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

This way the comma is not "loose".

Maintaining natural order of data

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

ORDER BY (SELECT 0)

Help understanding query

When data comes from different sources you may want to know which part of the query that resulted in that row, then use a column just for identification:

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

Virtual Column

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

As before, SELECT can catch any expression, not just the table fields. These expressions may 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 some way by the application and will be used by it or will serve for some decision at the time the data is returned to writing in the database

Caputo's answer gives more details about this possibility.

    
08.12.2014 / 17:58
9

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 of the parsed fields to 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 resulted

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

Okay, I get it. But for what?

This can be used as a device to:

  • Fill this field in RunTime via code
  • Bring a field expected by legacy code
  • Meet a data model where this field would not be needed
  • Have a field to be checked or unchecked in the interface
  • 08.12.2014 / 17:57