Character family datatype and NULL in Union statement

Conclusion: The default datatype of NULL is INT (wait lets first prove it, we might be wrong).

Yesterday my teammate called me and showed me a strange behavior of NULL with character datatype in union clause (in Teradata database) and this strange behavior is applicable to most of databases (NOT in SQL Server  J). Let’s try to stimulate the same scenario.

Here we have below Union statement

select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4

union

select 'x' as col1, 'y' as col2, null, null

col1 col2 col3        col4

---- ---- ----------- -----------

a    b    100         200

x    y    NULL        NULL

(2 row(s) affected)

There are four columns in query, first two columns are of character family datatype and col3 and col4 are from Integer family datatype.

Now look at the second query

select 'x' as col1, 'y' as col2, null, null

Here, notice that first two columns are of char datatype and rest two columns are of NULL and union worked perfectly.

Lets revise the union rule:

1.        Column number should match in all select query involved in union

2.        The datatype of column in one select query should match with other select query participating in UNION.

Now return back to our first select statement in UNION query

select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4

So by Union rule book, any query doing union with above query should have first two columns as character datatype and last two columns as INT datatype.

Now if you see our second query

select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4

union

select 'x' as col1, 'y' as col2, null, null

First two columns are of character datatype which matches with the datatype of first query and unlike first two columns, last two are NULL which doesn’t match with the datatype of first query col3 and col4 datatype. Still query work fine.

There could be two reason why query worked fine:

1.  NULL is compatable with INT datatype
2.             The is an implicit conversion happening with NULL and INT datatype, something like this ( cast( NULL as INT)

Let dig it further by rewriting the query

select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4

union

select 'x', NULL, NULL, NULL

Above query fails, that could means

1. Compiler is able to do implicit conversion of NULL to character database
2. NULL is not compatible to Character family datatype.

Now let’s rewrite the query and cast the NULL in second column of second query

select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4

union

select 'x', cast (NULL as varchar(1)), NULL, NULL

col1 col2 col3        col4

---- ---- ----------- -----------

a    b    100         200

x    NULL NULL        NULL

The query work fine.

We can inference from above set of query that NULL is compatible with INT but not with CHAR datatype.

Let find out the root clause of this.

The question must be in your mind must be: Does NULL has any DEFAULT datatype? Lets try to find out this answer.

Here I am storing result of query in temporary table #tempotable

select null as col1 into #tempotable

Lets now see the table and column property of #tempotable

TABLE_QUALIFIER      TABLE_OWNER   COLUMN_NAME   TYPE_NAME     PRECISION     LENGTH

-----------------------------------------------------------------------

tempdb               dbo            col1          int           10            4

J here we can see Datatype of NULL is treated as INT Type with 4 byte length.

What we concluded, default datatype of NULL is INT??

Wait before concluding anything so early J

Let declare a variable of variant type and assign it with NULL

declare @var sql_variant = NULL

Now let find out the datatype of @Var variable

select sql_variant_property(@var, 'Basetype') as TypeName,

     sql_variant_property(@var, 'Precision') as Precision,

Output:

Type Name    Precision

------------------------------

NULL              NULL               

Now this shows NULL has no datatype J

What we concluded: NULL has no datatype, but when it takes part in forming result set, the compiler consider NULL as INT datatype with 4 byte length and 10 precision.

NOTE: SQL Server 2005 had this issue, in all later versions they rectified this issue and have high degree of NULL handling Enjy coding…SQL J

Post Reference: Vikram Aristocratic Elfin Share

Group by with Union

There is always a question for fresh SQL Engineers, how do I add group by with Union statement, doing this throw error.

It is quite possible and very easy to implement. Lets take an example to check this

Here we are creating two table TableA and TableB with two fileld id and name, then we will do the union with group by

create table TableA

(id int,

name varchar(15))

Command(s) completed successfully.

create table TableB

(id int,

name varchar(15))

Command(s) completed successfully.

Inserting few rows into TableA

insert into TableA values(1,'Prachi')

insert into TableA values(2,'Shilpa')

insert into TableA values(3,'Bhagyashree')

  

Inserting few rows into TableB

insert into TableB values(1,'Pragyan')

insert into TableB values(3,'Subankeri')

insert into TableB values(4,'Neha')

Now I want to do union of these two tables and group the result by ID

select id,name from TableA

union all

select id,name from TableB

group by id

Msg 8120, Level 16, State 1, Line 3

Column 'TableB.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Let see what an alternative is

select * from

(select id,name from TableA

union all

select id,name from TableB)as FinalTable(id,name)

group by id,name

Conclusion:  we can use group by after putting the union query result in a select statement thereafter doing group by.

Programmer born alone as a warrior, live alone as a fighter  and die alone as a calm, sometime in the course of his programming life he create a false impression for the  moment that he is not unaided, he too has girl friend to love beyond code.  

Post Reference: Vikram Aristocratic Elfin Share

Sorting table separately while using UNION All, How to??

There are times when developer want top and bottom query of the UNION ALL resultset sorted independently, like this

select Columns from table1 order by Columns

union all

select Columns from tabbe2 order by Columns

However the above query will fail and give the output as

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'union'.

NOTE :It is not possible to have two different order by in the UNION statement because UNION returns single resultant.

However if your requirement is saying to sort top and bottom query independently then how to do?

Let’s create a scenario, by taking two tables TABLE1 and TABLE2

create table table1

(id int,

name varchar(15)

)

Command(s) completed successfully.

create table table2

(id int,

name varchar(15)

)

Command(s) completed successfully.

insert into table1(id,name)

select 1,'Ananya_Tab1'

union all

select 2,'Abhiroop_Tab1'

union all

select 3,'Gunjal_Tab1'

(3 row(s) affected)

insert into table2(id,name)

select 3,'Bikshapati_Tab2'

union all

select 2,'Sanjana_Tab2'

union all

select 1,'Akshit_Tab3'

(3 row(s) affected)

Now our tables are in place let’s do proceed to stimulate our requirement, we want like this  

select id,name, 'tab1' as odrCol from table1

order by id

union all

select id,name, 'tab2' as odr1 from table2

order by id

But firing the above script on ssms, gives error                          

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'union'.

And if we execute the select UNION without order by

select id,name from table1

union all

select id,name from table2

id          name

----------- ---------------

1           Ananya_Tab1

2           Abhiroop_Tab1

3           Gunjal_Tab1

3           Bikshapati_Tab2

2           Sanjana_Tab2

1           Akshit_Tab3

(6 row(s) affected)

However our requirement is like this

id          name          

----------- ---------------

1           Ananya_Tab1    

2           Abhiroop_Tab1  

3           Gunjal_Tab1    

1           Akshit_Tab3    

2           Sanjana_Tab2   

3           Bikshapati_Tab2

So to get the output as required, lets add a additional column ordCol and use it in order by clause

select id,name, 'tab1' as odrCol from table1

union all

select id,name, 'tab2' as odrCol from table2

order by odrCol,id

id          name            odrCol

----------- --------------- ------

1           Ananya_Tab1     tab1

2           Abhiroop_Tab1   tab1

3           Gunjal_Tab1     tab1

1           Akshit_Tab3     tab2

2           Sanjana_Tab2    tab2

3           Bikshapati_Tab2 tab2

(6 row(s) affected)

Now we can see we achieved our desired requirement of independent sorting of select query in UNION ALL.

Code exercises your creative instincts; do code n enjy J   

Post Reference: Vikram Aristocratic Elfin Share