Display NULL values in a query

Transact-SQL
1

when we do select * from table how does the null value display? starting of the result or towards the end of the result?

2

If you have any NULL values in your columns/rows within your table, it will display NULL. not sure what you mean by starting or end of result?
Pasi.

1 Like
3

I mean rows with 'null values display first' or ' non null values display first'?

4

By default, NULL will sort before / ahead of all other values.

5

Note that, by default, nothing is ordered at all and values can appear anywhere. If you want things sorted, you MUST include an Order By clause.

1 Like
6

to find this out !!! lets do some testing !!!

drop table #data
go 

create table #data 
(
column_for_testing varchar(100) null
)
go 

INSERT INTO #data SELECT 'Ok fine'
INSERT INTO #data SELECT null
INSERT INTO #data SELECT 'so sad'
INSERT INTO #data SELECT null
INSERT INTO #data SELECT null
INSERT INTO #data SELECT 'Now what'
go 

SELECT 'Testing where nulls Appear ', * from #data

image

as you can see nulls appear
...in the order in which data .
... inserted into the table ...

unless you specify ... something !!!! ( like order by etc )

7

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

In Oracle the correct way to test a NULL value is to use the IS NULL or IS NOT NULL test.

Select column values that are NULL:

SELECT * FROM emp WHERE comm IS NULL ;