SQL Tip #9 – All I know about SQL NULL

NULL indicates missing/unknown/inapplicable/absent.

If the above statement makes you think that NULL is useless, then you are mistaken. This is one of the thing that no database developer can avoid it. So it very important to understand how NULL behaves differently in various situations.

Simply we can say, NULL is a placeholder for missing or unknown values at any given time.

At the time of the table creation if we know that values for certain columns won’t be known or will be missing during record insertion then we can make those columns to allow NULL. Later NULL could be replaced with the meaningful values.

Takeaway for NULL:

  • It is very important not to get confused NULL as an space or empty string.
  • NULL is not equal to NULL.
  • Any operation involving NULL will return NULL
  • Comparison operators (<, >, =, <>) cannot be used to find records with the NULL values. IS NULL or IS NOT NULL operators is the way to go.
  • NULL will be ignored for most of the aggregate functions. However, COUNT(*) will include NULL values.

 

Following examples below will return NULL:

SELECT 'Sumon' + NULL
SELECT 0 + NULL
SELECT 10 * NULL

SQL Tip #8 – Only Unique Records

Once I had the requirements to pick a customer per city. In order to solve this problem I came up with the following solutions.

Sample table 1:

Output 1:

 

Solution 1:

SELECT Main.* FROM Customer Main
INNER JOIN
	(
		SELECT MIN(CustomerID) AS CustomerId
		FROM Customer GROUP BY City
	) Sub
ON Main.CustomerId = Sub.CustomerId
ORDER BY Main.CustomerId

 

Solution 2:

SELECT Main.* FROM Customer Main
CROSS APPLY
	(
		SELECT MIN(CustomerID) AS CustomerId
		FROM Customer
		WHERE City = Main.City
		GROUP BY City
	) Sub
WHERE Main.CustomerId = Sub.CustomerId
ORDER BY Main.CustomerId

 

Solution 3:

SELECT Main.* FROM Customer Main
CROSS APPLY
	(
		SELECT ROW_NUMBER()
		OVER(PARTITION BY City
		     ORDER BY CustomerId ASC) AS Rnk,
		CustomerId
		FROM Customer
	) Sub
WHERE Main.CustomerId = Sub.CustomerId
      AND Sub.Rnk = 1
ORDER BY Main.CustomerId

 

Solution 4:

SELECT CustomerId,CustomerName,City FROM
(
		SELECT ROW_NUMBER()
		OVER(PARTITION BY City
		     ORDER BY CustomerId ASC) AS Rnk,
		*
		FROM Customer
) Main
WHERE Main.Rnk = 1
ORDER BY Main.CustomerId

 

Performance of these query will vary based on the database size, indexing etc. You can pick the query that suits you most.

SQL Tip #7 – Formatting numbers with leading zeros (Left Padding)

In order to format a number with leading zeros, one of the following solutions could be used:

Solution 1:

DECLARE @number INT
DECLARE @length INT
SET @number = 123
SET @length = 10

SELECT REPLACE (STR( @number, @length ), ' ' , '0' ) 
       AS NewNumber

Solution 2:

DECLARE @number INT
DECLARE @length INT
SET @number = 123
SET @length = 10

SELECT RIGHT('0000000000'+ 
       CONVERT(VARCHAR,@number),@length) 
       AS NewNumber 

Solution 3:

DECLARE @number INT
DECLARE @length INT
SET @number = 123
SET @length = 10

SELECT REPLICATE('0',@length - LEN(@number)) + 
       CONVERT(VARCHAR,@number) 
       AS NewNumber

SQL Tip #6 – Transform Rows into Comma Separated Value (CSV)

This tutorial is about converting row values into a single column in the form of comma separated values (CSV).

Sample table 1:

Output 1:

Above output can be achieved with the help of the following:
1. For XML path
2. Stuff

For XML path(”) is used to concatenate rows values into one column.
Stuff – this function helps to replace a part of string with another string.

In order to achieve we can apply the following sql:

SELECT Main.City,
(
	SELECT ',' + c.CustomerName
	FROM Customer c
	WHERE c.City = Main.City
	FOR XML PATH('')

) AS CSV
FROM Customer AS Main
GROUP BY Main.City

We have achieved the expected result. However we can see an additional comma in the result set.

We can easily overcome this issue with the help of Stuff function.

SELECT Main.City, STUFF(
(
	SELECT ',' + c.CustomerName
	FROM Customer c
	WHERE c.City = Main.City
	FOR XML PATH('')

),1,1,'') AS CSV
FROM Customer AS Main
GROUP BY Main.City