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