On an initial view it looks like both functions do the same thing except that COALESCE takes more arguments.
ISNULL(expression, replacement)
COALESCE(expression1, expression2 [,.... more expressions])Let's do an example. Firstly we need some data
CREATE TABLE Person ( Name varchar(20) NOT NULL, Postcode varchar(10) NULL ) GO INSERT INTO Person VALUES ('Karen', 'PA2 8NR') INSERT INTO Person VALUES ('Julie', NULL) INSERT INTO Person VALUES ('Mauda', NULL) INSERT INTO Person VALUES ('Ali', NULL) INSERT INTO Person VALUES ('Heidi', '2000') INSERT INTO Person VALUES ('Helen', NULL) INSERT INTO Person VALUES ('Rhona', 'G12 9SR') GOAnd if we run the following SQL we get the same results
SELECT Name, ISNULL(Postcode, 'None') FROM Person SELECT Name, COALESCE(Postcode, 'None') FROM Person
Name -------------------- ---------- Karen PA2 8NR Julie None Mauda None Ali None Heidi 2000 Helen None Rhona G12 9SRThe most obvious first difference is that ISNULL takes two parameters - an expression to check against (which can be any type), and an expression to return if the first parameter is NULL. The type of the replacement must be the same as the first expression or implicitly convertible to it. So the following can be done
SELECT Name, ISNULL(Postcode, 0) FROM PersonAnd will return the string '0' - converting the number 0 to a string. Doing this with COALESCE will
SELECT Name, COALESCE(Postcode, 0) FROM PersonGives the following error message
Name -------------------- ----------- Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'PA2 8NR' to data type int.Another point related to types will be apparent with this code sample
SELECT Name, ISNULL(Postcode, 'No Postcode') FROM Person SELECT Name, COALESCE(Postcode, 'No Postcode') FROM PersonThe output looks like this
Name -------------------- ---------- Karen PA2 8NR Julie No Postcod Mauda No Postcod Ali No Postcod Heidi 2000 Helen No Postcod Rhona G12 9SR Name -------------------- ----------- Karen PA2 8NR Julie No Postcode Mauda No Postcode Ali No Postcode Heidi 2000 Helen No Postcode Rhona G12 9SRThe second set of results shows the output from COALESCE - which has the full text of "No postcode" whereas ISNULL has truncated it (to 10 characters) - which is the type of the column specified (Postcode) which is varchar(10).
The type has another knock on effect you might need to be aware of. If you use the type in a computed column then ISNULL will use the type of the first column (and the type created will be non-nullable). Let's create a table with as computed column. When you do this then you do not specify the type - SQL will infer the type from the result of the expression.
In the case of ISNULL this will be the type of the first parameter.
CREATE TABLE Person2 ( Name varchar(20) NOT NULL, Postcode varchar(10) NULL, NotNullPostcode AS ISNULL(Postcode, 'None')) GO select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Person2' AND COLUMN_NAME='NotNullPostcode'
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE --------------- --------------- ------------------------ ----------- NotNullPostcode varchar 10 NOEven if you change the literal 'None' to something greater than 10 characters then it will only ever be 10 characters (the type of the first expression - in this case the type of the column Postcode).
Doing this with Coalesce will take the result of the COALESCE statement.
CREATE TABLE Person3 ( Name varchar(20) NOT NULL, Postcode varchar(10) NULL, NotNullPostcode AS COALESCE(Postcode, 'None')) GO select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Person3' AND COLUMN_NAME='NotNullPostcode'
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE --------------- --------------- ------------------------ ----------- NotNullPostcode varchar 10 YESFirst thing to note is that even though we have specified a literal ('None') - the column is set as being Nullable. COALESCE will always be thought of returning a NULL - if any expression in the list can return a NULL. If all expressions are Non-Nullable then the resultant column won't allow nulls. Why you would then use COALESCE is then in doubt?
The Postcode column is longer than the literal 'None' - so the size is set to 10. Now let's make the literal larger in size.
CREATE TABLE Person3 ( Name varchar(20) NOT NULL, Postcode varchar(10) NULL, NotNullPostcode AS COALESCE(Postcode, 'No postcode has been specified')) GO select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Person3' AND COLUMN_NAME='NotNullPostcode'
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE --------------- --------------- ------------------------ ----------- NotNullPostcode varchar 30 YESThe type of an expression (especially the null-ability) is important when used in queries as the execution plan (how SQL thinks the best way to execute it) will be different.
As we have said the main difference between COALESCE and ISNULL is the number of parameters. COALESCE will look at each parameter (in order) and return the first expression that doesn't evaluate to NULL (or NULL if they all evaluate to NULL).
COALESCE is converted by SQL into a CASE statement. Thus the SQL below
SELECT Name, COALESCE(Postcode, 'None') FROM Personis the same as
SELECT Name, CASE WHEN(Postcode IS NOT NULL) THEN Postcode ELSE 'None' END FROM PersonThis means that each expression is evaluated at least once and one may be evaluated a second time (in this case if Postcode is not null then it is evaluated to determine if it is not null and then evaluated again to return it). Another downside of this is that if you have an expression with sub-queries you may get different answers when the sub-query is evaluated a second time. ISNULL on the other hand is evaluated only once.
When to use?
An understanding of the differences of ISNULL and COALESCE is useful for the occasions when the differences are important. But why use them at all.One use is the one given above - change some text. In this case when you don't know the postcode of the person. Remember when you deal with Nulls the result of an expression containing a NULL is also a NULL. So if you have an address as well you cannot concatenate the strings.
Additionally, aggregate functions (such as Average) will work with all the rows where the column in question is Not Null. Consider this example
CREATE Table Numbers (Number float NULL) GO INSERT INTO Numbers VALUES (4) INSERT INTO Numbers VALUES (2) INSERT INTO Numbers VALUES (3) INSERT INTO Numbers VALUES (NULL) INSERT INTO Numbers VALUES (6) INSERT INTO Numbers VALUES (8)The sum of the numbers is 23. If you run this SQL to ask for the average
SELECT AVG(Number) FROM NumbersYou get
4.6Which is the result of 23/5. But using ISNULL to check Number first
SELECT AVG(ISNULL(Number,0)) FROM NumbersWill now give you
3.83333333333333Which is 23/6
No comments:
Post a Comment