T-SQL: ISNULL, COALESCE, and NULLIF

The following test demonstartes the usage of ISNULL, COALESCE, and NULLIF of T-SQL. For the complete list of my other tests and the creation scripts of TSQL2012 database, please refer to my T-SQL Script List.

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Select and execute the following query to illustrate
-- The ISNULL function
SELECT custid, city, ISNULL(region, 'N/A') AS region, country
FROM Sales.Customers;

-- Step 3: Select and execute the following query to illustrate the
-- COALESCE function
SELECT custid, country, region, city,
country + ',' + COALESCE(region, ' ') + ', ' + city as location
FROM Sales.Customers;

-- Step 4: Select and execute the following queries to illustrate the
-- NULLIF function

-- First, set up sample data
CREATE TABLE dbo.employee_goals(emp_id INT , goal int, actual int);

GO
-- Populate the sample data
INSERT INTO dbo.employee_goals
VALUES(1,100, 110), (2,90, 90), (3,100, 90), (4,100, 80);

-- Show the sample data
SELECT emp_id, goal, actual
FROM dbo.employee_goals;

-- Use NULLIF to show which employees have actual
-- values different from their goals
SELECT emp_id, NULLIF(actual,goal) AS actual_if_different
FROM dbo.employee_goals;

-- Step 5: Clean up demo table
DROP TABLE dbo.employee_goals;

About henry416
I am a computer technology explorer and an university student based on Toronto. If you have any question, please feel free to discuss and comment here

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s