SQL: What does NULL as ColumnName imply

Question

I understand that AS is used to create an alias. Therefore, it makes sense to have one long name aliased as a shorter one. However, I am seeing a SQL query NULL as ColumnName What does this imply?

SELECT *, NULL as aColumn

Yawning

can you show the whole query or something more... like select '1' as numbering from tablename – Possie

@sac added query as it stands – Yawning

I think LittleBobbyTables's answer is right, it is a NULL value for aColumn for all rows... – Possie

Answer 1

Aliasing can be used in a number of ways, not just to shorten a long column name.

In this case, your example means you're returning a column that always contains NULL, and it's alias/column name is aColumn.

Aliasing can also be used when you're using computed values, such as Column1 + Column2 AS Column3.

Semibreve

I think so too ;) As to why they wrote the query like that, it's anyone's guess. As it's written, it really doesn't do anything other than return all the columns, plus an extra column called aColumn. Without seeing any surrounding code or business processes, it's hard to guess what the intent of the query is. – Semibreve

And the datatype of this column is int – Deathful

Answer 2

When unioning or joining datasets using a 'Null AS [ColumnA] is a quick way to make sure create a complete dataset that can then be updated later and a new column does not need to be created in any of the source tables.

Wolfson

Answer 3

Query result can have a new column that has all NULL values. In SQL Server we can do it like this

SELECT *, CAST(NULL AS <data-type>) AS as aColumn

e.g.

SELECT *, CAST(NULL AS BIGINT) AS as aColumn

Argillite

Answer 4

It means that "aColumn" has only Null values. This column could be updated with actual values later but it's an empty one when selected.

---I'm not sure if you know about SSIS, but this mechanism is useful with SSIS to add variable value to the "empty" column.

Ankus

just to be sure, SSIS means: SQL Server Integration Services ? – Yawning

Yes. A query as you posted could work as source table, then variable set in SSIS could be used to provide values for the empty column. – Ankus

Answer 5

When using SELECT you can pass a value to the column directly. So something like :

SELECT ID, Name, 'None' AS Hobbies, 0 AS NumberOfPets, NULL AS Picture, '' AS Adress

Is valid.

It can be used to format nicely a query output when using UNION/UNION ALL.

Jacquelynejacquelynn

Answer 6

In the statement result we have a column that has all NULL values. We can refer to that column using alias.

In your case the query selects all records from table, and each result record has additional column containing only NULL values. If we want to refer to this result set and to additional column in other place in the future, we should use alias.

Cuticula

Answer 7

How about without using the the as SELECT ID , Name , 'None' AS Hobbies , 0 AS NumberOfPets , NULL Picture

Nonrecognition

Answer 8

Usually adding NULL as [Column] name at the end of a select all is used when inserting into another table a calculated column based on the table you have just selected.

UPDATE #TempTable SET aColumn = Column1 + Column2 WHERE ...

Then exporting or saving the results to another table.

Upsweep