SQL Blog: NULL or IS NULL or IS NOT NULL

SQL Blog: NULL or IS NULL or IS NOT NULL

NULL or IS NULL or IS NOT NULL

Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information".

When you perform operations on null in SQL Server, by default the result is NULL. NULL values can not really be evaluated.

SQL implements three logical results, so SQL implementations must provide for a specialized three-valued logic (3VL). The rules governing SQL three-valued logic are shown in the tables below (p and q represent logical states)

Joins

SQL outer joins, including left outer joins, right outer joins, and full outer joins, automatically produce Nulls as placeholders for missing values in related tables. For left outer joins, for instance, Nulls are produced in place of rows missing from the table appearing on the right-hand side of the LEFT OUTER JOIN operator. The following simple example uses two tables to demonstrate Null placeholder production in a left outer join.

The first table (Employee) contains employee ID numbers and names, while the second table (PhoneNumber) contains related employee ID numbers and phone numbers, as shown below.

Employee ID LastName FirstName 1 Johnson Joe 2 Lewis Larry 3 Thompson Thomas 4 Patterson Patricia

PhoneNumber ID Number 1 555-2323 3 555-9876

The result set generated by this query demonstrates how SQL uses Null as a placeholder for values missing from the right-hand (PhoneNumber) table, as shown below.

Inner joins and cross joins, also available in standard SQL, do not generate Null placeholders for missing values in related tables.

Care must be taken when using null able columns in SQL join criteria. Because a Null is not equal to any other Null, Nulls in a column of one table will not join to Nulls in the related column of another table using the standard equality comparison operators. The SQL COALESCE function or CASE expressions can be used to "simulate" Null equality in join criteria, and the IS NULL and IS NOT NULL predicates can be used in the join criteria as well.

Another Example:-

 Source:-

1.        http://en.wikipedia.org

2.        http://blogs.msdn.com

Date Format for Financial Year

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Nitin Kurle
-- Create date: May 18 2011 12:02:25:140PM
-- Description: Here to Display Financial Year Date Range from Current Year to Previuos Year.
-- =============================================
CREATE PROCEDURE [dbo].[prDateCombination] (
@Dt as varchar(15)
)
AS
DECLARE
@FromDate as varchar(15),
@ToDate as varchar(15),
@PrevFromDate as varchar(15),
@PrevToDate as varchar(15),
@DebugMode as int,
@strSql as nvarchar(max),
@CurrentYrDt as varchar(15),
@PreviousYrDt as varchar(15)
BEGIN
Print 'Dates Start ==>> ' + convert(varchar,getdate(), 109)

set @FromDate = N''
set @ToDate = N''
IF MONTH(@Dt) >= 4  SELECT @FromDate = CONVERT(CHAR(4), YEAR(@Dt)) + '-04-01'
ELSE SELECT @FromDate = CONVERT(CHAR(4), YEAR(@Dt)-1) + '-04-01'
PRINT ' @FromDate ----->>>>  '+@FromDate

set @ToDate = cast((year(@Dt)) as varchar) + '-' + cast(month(@Dt) as varchar) + '-' + cast(day(@Dt) as varchar)
PRINT ' @ToDate ----->>>>  '+@ToDate

IF MONTH(@Dt) >= 4  SELECT @PrevFromDate = CONVERT(CHAR(4), YEAR(@Dt)-1) + '-04-01'
ELSE SELECT @PrevFromDate = CONVERT(CHAR(4), YEAR(@Dt)-2) + '-04-01'
PRINT ' @PrevFromDate ----->>>>  '+@PrevFromDate

set @PrevToDate = cast((year(@Dt)-1) as varchar) + '-' + cast(month(@Dt) as varchar) + '-' + cast(day(@Dt) as varchar)
PRINT ' @PrevToDate ----->>>>  '+@PrevToDate
PRINT ' -------------------------------------------------------------------- '

set @PreviousYrDt = N''
set @CurrentYrDt = N''

set @CurrentYrDt = convert(varchar,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@ToDate)+1,0)),23)
PRINT ' @CurrentYrDt ----->>>>  '+@CurrentYrDt

set @PreviousYrDt = cast((YEAR(@CurrentYrDt)-1) as varchar) + '-' + cast(MONTH(@CurrentYrDt) as varchar) + '-' + cast(DAY(@CurrentYrDt) as varchar)
PRINT ' @PreviousYrDt ----->>>>  '+@PreviousYrDt

Print 'Dates Complete ==>> ' + convert(varchar,getdate(), 109)

END


-- Execute this Procedure for following Method
--EXEC prDateCombination '15-Jan-2011'

Output :-

Date Format ie getdate()

Hi, Friends..

Here Display All Formated Date in using gatDate() Function.

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)

                                        -- Oct  2 2010 11:01AM          

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2010                  

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.10.02           

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

                                        -- Oct  2 2010 11:02:44:013AM   

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

-- yyyymmdd - ISO date format - international standard - works with any language setting

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

                                        -- 02 Oct 2010 11:02:07:577     

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

                                        -- 2010-10-02T10:52:47.513

-- Without century (YY) date / datetime conversion - there are exceptions!

SELECT convert(varchar, getdate(), 0)   -- mon dd yyyy hh:mmAM (or PM)

SELECT convert(varchar, getdate(), 1)   -- mm/dd/yy

SELECT convert(varchar, getdate(), 2)   -- yy.mm.dd          

SELECT convert(varchar, getdate(), 3)   -- dd/mm/yy

SELECT convert(varchar, getdate(), 4)   -- dd.mm.yy

SELECT convert(varchar, getdate(), 5)   -- dd-mm-yy

SELECT convert(varchar, getdate(), 6)   -- dd mon yy

SELECT convert(varchar, getdate(), 7)   -- mon dd, yy

SELECT convert(varchar, getdate(), 8)   -- hh:mm:ss

SELECT convert(varchar, getdate(), 9)   -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 10)  -- mm-dd-yy

SELECT convert(varchar, getdate(), 11)  -- yy/mm/dd

SELECT convert(varchar, getdate(), 12)  -- yymmdd

SELECT convert(varchar, getdate(), 13)  -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 14)  -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 20)  -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 21)  -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 22)  -- mm/dd/yy hh:mm:ss AM (or PM)

SELECT convert(varchar, getdate(), 23)  -- yyyy-mm-dd

SELECT convert(varchar, getdate(), 24)  -- hh:mm:ss

SELECT convert(varchar, getdate(), 25)  -- yyyy-mm-dd hh:mm:ss.mmm 

-- SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm dd

SELECT convert(varchar(7), getdate(), 126)                 -- yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8)          -- mon yyyy

SELECT substring(convert(varchar, getdate(), 120),6, 11)

SELECT US = convert(VARCHAR,convert(DATETIME,'01/12/2015'))

How to Create View..

Hi,

Frendzzz

--How to Merge Two Table Concurrenlty, and this data Stored in given Views..
--1st create tow table & insert the data given as the following..
--After tat Create as View as Criteria..

create table FRUITS(
Number int identity(1,1),
Name varchar(25),
Rate numeric(10,2),
Data varchar(50)
);

insert into FRUITS values('Apple Fiesta','125.48','Contains FRUIT PRODUCE')

insert into FRUITS values('Celeriac','58.48','Contains FRUIT AND VEGETABLE PRODUCE')
insert into FRUITS values('Carrots Small','458.26','Contains VEGETABLE PRODUCE')
insert into FRUITS values('Exotic Casava','986.25','Contains FRUIT PRODUCE')
insert into FRUITS values('Herbs Lovage','1025.32','Contains FRUIT AND VEGETABLE PRODUCE')
insert into FRUITS values('Oranges Med','362.25','Contains VEGETABLE PRODUCE')
insert into FRUITS values('Apple Fiesta','256.38','Contains FRUIT PRODUCE')
insert into FRUITS values('Pears Furel','569.86','Contains FRUIT PRODUCE')
insert into FRUITS values('Tomato Plum','45.26','Contains VEGETABLE PRODUCE')
insert into FRUITS values('Turnips','563.18','Contains VEGETABLE PRODUCE')
insert into FRUITS values('Oranges Med','68.59','Contains FRUIT AND VEGETABLE PRODUCE')

update Fruits set Data='Flora India.com' where Data='Contains FRUIT AND VEGETABLE PRODUCE'
update Fruits set Data='Dry Scenario' where Data='Contains FRUIT PRODUCE'

select * from FRUITS

Output :-

create table DRYFRUITS(
Number int identity(1,1),
Name varchar(25),
Rate numeric(10,2),
Data varchar(50)
);

insert into DRYFRUITS values('Assorted Mini Dryfruits','630','Flora India.com')

insert into DRYFRUITS values('Assorted Mini Dryfruits','630','Flora India.com')
insert into DRYFRUITS values('1 Kg Almonds','1150','Flora India.com')
insert into DRYFRUITS values('1 Kg Raisins (Kishmish)','725','Dry Scenario')
insert into DRYFRUITS values('1/2 Kg Almonds','575','Flora India.com')
insert into DRYFRUITS values('Roasted Temptation','1250','Flora India.com')
insert into DRYFRUITS values('Unsatiable Desire','1900','Dry Scenario')
insert into DRYFRUITS values('1/2 Kg Pistachio','675','Flora India.com')
insert into DRYFRUITS values('1 Kg Raisins (Kishmish)','850','Dry Scenario')
insert into DRYFRUITS values('1 Kg Raisins (Kishmish)','650','Dry Scenario')
insert into DRYFRUITS values('1/2 Kg Almonds','1025','Dry Scenario')

select * from DRYFRUITS

Output:

-- How to create view ???
-- Syntax of View Click the following Url..
-- http://msdn.microsoft.com/en-us/library/aa258253(v=sql.80).aspx

Create View View_MergeFruits
As
SELECT     TOP (100) PERCENT dbo.DRYFRUITS.Name AS DryFruits, dbo.DRYFRUITS.Rate AS DryFruits_Rate, dbo.FRUITS.Name AS Fruits,
                      dbo.FRUITS.Rate AS Fruits_Rate, dbo.DRYFRUITS.Data
FROM         dbo.DRYFRUITS INNER JOIN
                      dbo.FRUITS ON dbo.DRYFRUITS.Data = dbo.FRUITS.Data
ORDER BY DryFruits, Fruits

--In that view <View_MergeFruits> all data are Secured Stored...
select * from View_MergeFruits

Output:

This is View Data..Stored Properly in View_MergeFruits

Parameterized Stored Procedure

hi frends..

1st create table <_Color> use following Query


create table _Color(
colorid int identity(1,1),
color varchar(10),
colorcodes varchar(7),
name varchar(20)
)

--drop table _Color

insert into _Color values('Yellow','#FFFF00','tshirt')
insert into _Color values('LightBlue','#0000FF','cell')
insert into _Color values('Brown','#804000','pen')
insert into _Color values('Yellow','#FFFF00','waterlemon')
insert into _Color values('LightBlue','#0000FF','ball')
insert into _Color values('Red','#FF0000','door')
insert into _Color values('Brown','#804000','Mobile')
insert into _Color values('Crimson','DC143C','watch')
insert into _Color values('Red','#FF0000','nip')
insert into _Color values('Yellow','#FFFF00','flowers')
insert into _Color values('LightBlue','#0000FF','chair')
insert into _Color values('Brown','#804000','eyes')

select * from _Color

Output :

Procedure Start Here..

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- =============================================
-- When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks,
-- and literals must be delimited by single quotation marks.
-- When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by
-- single or double quotation marks. If a literal string is delimited by double quotation
-- marks, the string can contain embedded single quotation marks, such as apostrophes.
-- =============================================

Alter PROCEDURE [dbo].[Parametric_Procedure](
@color as varchar(max)
)
with recompile
AS
DECLARE
@strsql nvarchar(max)
BEGIN
SET NOCOUNT ON;

set @strsql = N'select * from _Color '
set @strsql = @strsql + "where color = '" +@color+ "'"
print @strsql

exec sp_executeSql @strSql
END


OUTPUT :

How to write Stored Procedure in SQL SERVER

I will try to write store procedure in sql.It is for begineers purpose.

To begin write away to the procedure you will 1st write CREATE PROCEDURE <PROCEDURE_NAME>

--Here i will try to display simple example of Parametric procedure (SQL SERVER 2005)


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE procedure_first
-- Add the parameters for the stored procedure here
(
@firstname as varchar(max),
@lastname as varchar(max)
)
with recompile
AS
DECLARE
@strsql nvarchar(max)
BEGIN
SET NOCOUNT ON;

set @strsql = N'select * from ex '
set @strsql = @strsql + "where firstname = '" +@firstname+ "' and lastname = '" +@lastname+ "'"

       print @strsql

exec sp_executeSql @strSql
END
GO

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

How to Execute?
Fire Following Command.

EXEC dbo.procedure_first 'Sagar','Awale'

Output :

and Message also displayed (plz check next image..)

Subqueries in CASE and BETWEEN Statements in SQL SERVER 2008

Hi Frendz...
Check the Following Queries...
-------------------------------------------------------------
First Creating 2 tables..


CREATE TABLE T1 (A INT, B1 INT, B2 INT)
CREATE TABLE T2 (A INT, B INT)

Insert Values In T1 Table..


insert into T1 values(11,65,55)
insert into T1 values(45,45,6)
insert into T1 values(14,9,98)
insert into T1 values(45,24,31)
insert into T1 values(133,5,4)

SELECT * FROM T1

------------------------------------------------------------
Insert Values In T2 Table..


insert into T2 values(11,55)
insert into T2 values(45,6)
insert into T2 values(14,9)
insert into T2 values(24,31)
insert into T2 values(133,4)

SELECT * FROM T2

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

• Subquery Using BETWEEN Statement

SELECT *

FROM T1

WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) BETWEEN T1.B1 AND T1.B2

SELECT *

FROM T1

WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) >= T1.B1 AND

    (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) <= T1.B2

SELECT Q.A, Q.B1, Q.B2

FROM

    (

    SELECT *, (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) SUM_B

    FROM T1

    ) Q

WHERE SUM_B BETWEEN Q.B1 AND Q.B2

SELECT T1.*

FROM T1 CROSS APPLY (SELECT SUM(T2.B) SUM_B FROM T2 WHERE T2.A = T1.A) Q

WHERE Q.SUM_B BETWEEN T1.B1 AND T1.B2

SELECT T1.*

FROM T1, (SELECT T2.A, SUM(T2.B) SUM_B FROM T2 GROUP BY T2.A) Q

WHERE T1.A = Q.A AND Q.SUM_B BETWEEN T1.B1 AND T1.B2

All Queries has Same Following Output..

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

• Subquery Using CASE Statement

SELECT *,

    CASE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A)

        WHEN T1.B1 THEN 'B1'

        WHEN T1.B2 THEN 'B2'

        ELSE NULL

    END CASE_B

FROM T1

SELECT Q.A, Q.B1, Q.B2,

    CASE Q.SUM_B

        WHEN Q.B1 THEN 'B1'

        WHEN Q.B2 THEN 'B2'

        ELSE NULL

    END CASE_B

FROM

    (

    SELECT *, (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) SUM_B

    FROM T1

    ) Q

SELECT T1.*,

    CASE Q.SUM_B

        WHEN T1.B1 THEN 'B1'

        WHEN T1.B2 THEN 'B2'

        ELSE NULL

    END CASE_B

FROM T1 CROSS APPLY (SELECT SUM(T2.B) SUM_B FROM T2 WHERE T2.A = T1.A) Q

Output :

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

SELECT T1.*,

    CASE Q.SUM_B

        WHEN T1.B1 THEN 'B1'

        WHEN T1.B2 THEN 'B2'

        ELSE NULL

    END CASE_B

FROM T1, (SELECT T2.A, SUM(T2.B) SUM_B FROM T2 GROUP BY T2.A) Q

WHERE T1.A = Q.A

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

Sub-queries in CASE and BETWEEN Statements in SQL SERVER 2008

Convert Varchar to Numeric in SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER Off
GO
alter FUNCTION [dbo].[Return_Example]
(

@a numeric(18,4),
@b numeric(18,4)
)
RETURNS numeric(18,3)
AS
BEGIN
declare
@division int,
@remainder int,
@Num2 varchar(100),
@c numeric(18,4)
set @division=floor(@a/@b)
set @remainder=@a%@b
--Here output will be a concatenated
set @num2 = cast(@division as varchar) + '.'+ cast(@remainder as varchar)
set @c=cast(@Num2 as numeric(18,3))
RETURN @c
END

-------------- Run As ---------

declare @a as varchar(100)
set @a = dbo.Return_Example(59,18)
print @a



OUTPUT :
3.500

Pivot In SQL SERVER

Hi Frendz..

HERE is Final Table #Example & it's Pivot.. 


declare @columnheader varchar(max)

Select @columnheader = COALESCE(@columnheader + ',isnull([' + cast(@FieldName as varchar) + '],0)','isnull([' + cast(@FieldName as varchar)+ '],0)')
FROM #Example

 DECLARE @query VARCHAR(max)
SET @query = 'SELECT * into fianltable FROM #Example
PIVOT
 (
 SUM(Col1)  
 FOR [MergeValue]  IN (' + @columnheader + ')
 )
 AS p order by ' + @columnheader + ''

EXECUTE(@query)


select * from fianltable
drop table fianltable

Display Last 3 Records in Table

--Display Last 3 Records in Table

create table #table
(
no int identity (1,1),
name varchar(20),
addr varchar(100)
)

insert into #table values ('ABC','Pune')
insert into #table values ('XYZ','mumbai')
insert into #table values ('KLM','Satara')
insert into #table values ('OPQR','Hyderabad')
insert into #table values ('JKLM','Sholapur')
insert into #table values ('GHIJ','Pune')
insert into #table values ('RETU','Bombai')

select * from #table

select top 3 * from #table order by no desc

--OUTPUT IS
--
--7 RETU Bombai
--6 GHIJ Pune
--5 JKLM Sholapur

storing the value into a variable within a select query

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE Pr_StoringValue
AS
declare @name varchar(max)
declare @name1 varchar(max)
BEGIN

create table #user(id integer IDENTITY(1,1),
username varchar(max)
)
insert into #user(username) values ('Nitin')
insert into #user(username) values ('Anoop')
insert into #user(username) values ('Sagar')

  select @name = username from #user
  select @name
  update #user set username = 'ABC' where id=3
  select @name1 = username from #user
  select @name1

select * from #user
END
GO

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

Execute Here...

EXEC Pr_StoringValue