Stack Overflow
Stack Overflow

Questions tagged [coalesce]

COALESCE is a SQL function that returns the first non-NULL expression among its arguments. COALESCE() is ANSI standard and may be used instead of vendor-specific alternatives such as ISNULL(), NVL() or IFNULL().

Return the first non-NULL argument. The COALESCE(expression1, expression2... expressionN) function is variety of the CASE expression.

Examples

Consider the expression: 

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   WHEN (expression2 IS NOT NULL) THEN expression2
   ...
   ELSE expressionN

It is equivalent to 

COALESCE(expression1, ... expressionN);

Another usage:

SELECT
  field1,
  COALESCE(SUM(customers), 0), -- if the SUM is NULL, it would be calculated as 0
  field3,
FROM
  ...

References

932 questions
307
votes
11 answers

Best way to check for "empty or null value"

What is best way to check if value is null or empty string in Postgres sql statements? Value can be long expression so it is preferable that it is written only once in check. Currently I'm using: coalesce( trim(stringexpression),'')='' But it looks…
Andrus
  • 26,339
  • 60
  • 204
  • 378
238
votes
8 answers

Oracle Differences between NVL and Coalesce

Are there non obvious differences between NVL and Coalesce in Oracle? The obvious differences are that coalesce will return the first non null item in its parameter list whereas nvl only takes two parameters and returns the first if it is not null,…
Tom Hubbard
  • 15,820
  • 14
  • 59
  • 86
216
votes
9 answers

How to concatenate columns in a Postgres SELECT?

I have two string columns a and b in a table foo. select a, b from foo returns values a and b. However, concatenation of a and b does not work. I tried : select a || b from foo and select a||', '||b from foo Update from comments: both columns…
Alex
  • 7,007
  • 18
  • 69
  • 114
208
votes
12 answers

?? Coalesce for empty string?

Something I find myself doing more and more is checking a string for empty (as in "" or null) and a conditional operator. A current example: s.SiteNumber.IsNullOrEmpty() ? "No Number" : s.SiteNumber; This is just an extension method, it's…
Nick Craver
  • 623,446
  • 136
  • 1,297
  • 1,155
197
votes
13 answers

How to get the first non-null value in Java?

Is there a Java equivalent of SQL's COALESCE function? That is, is there any way to return the first non-null value of several variables? e.g. Double a = null; Double b = 4.4; Double c = null; I want to somehow have a statement that will return the…
froadie
  • 79,995
  • 75
  • 166
  • 235
95
votes
1 answer

How to COALESCE for empty strings and NULL values?

I'm trying to make the fastest COALESCE() that accepts two or more arguments, and returns the first non-null AND non-empty ("") value. I'm using this: CREATE OR REPLACE FUNCTION coalescenonempty(VARIADIC in_ordered_actual varchar[]) RETURNS varchar…
MikeCW
  • 951
  • 1
  • 6
  • 3
87
votes
9 answers

Using ISNULL vs using COALESCE for checking a specific condition?

I know that multiple parameters can be passed to COALESCE, but when you want to to check just one expression to see if it doesn't exist, do you use a default or is it a better practice to use ISNULL instead? Is there any performance gain between…
JBone
  • 3,163
  • 11
  • 36
  • 47
84
votes
10 answers

Is there a coalesce-like function in Excel?

I need to fill a cell with the first non-empty entry in a set of columns (from left to right) in the same row - similar to coalesce() in SQL. In the following example sheet --------------------------------------- | | A | B | C | D…
Fabian
  • 2,822
  • 1
  • 17
  • 22
73
votes
2 answers

Using COALESCE to handle NULL values in PostgreSQL

I have the following query SELECT DISTINCT pt.incentive_marketing, pt.incentive_channel, pt.incentive_advertising FROM test.pricing pt WHERE pt.contract_id = 90000 group by 1,2,3 order by pt.incentive_marketing; The above…
ronan
  • 4,492
  • 13
  • 47
  • 67
64
votes
7 answers

?? Null Coalescing Operator --> What does coalescing mean?

I'm tempted to lie and say that English is my second language, but the truth is that I just have no idea what 'Coalescing' means. I know what ?? 'does' in C#, but the name doesn't make sense to me. I looked up the word and I understand it to be a…
Rob P.
  • 14,921
  • 14
  • 73
  • 109
64
votes
7 answers

Checking an input param if not Null and using it in where in SQL Server

What is the best way to include an input param in the WHERE clause but exclude it if it is null? There are a number of ways I believe, but I can't seem to remember then. Also could I use the COALESCE()? But I think this is only for SELECTing…
Martin
  • 23,844
  • 55
  • 201
  • 327
56
votes
9 answers

How to implement coalesce efficiently in R

Background Several SQL languages (I mostly use postgreSQL) have a function called coalesce which returns the first non null column element for each row. This can be very efficient to use when tables have a lot of NULL elements in them. I encounter…
while
  • 3,602
  • 4
  • 33
  • 42
42
votes
4 answers

How to use Coalesce in MySQL

A little help here. I really don't understand how to use this coalesce in MySQL I have read all the pages in page 1 result of how to use coalsece in google result. I know its meaning that it returns the first non-null value it encounters and null…
Belmark Caday
  • 1,623
  • 3
  • 21
  • 29
40
votes
7 answers

Changing a SUM returned NULL to zero

I have a stored procedure as follows: CREATE PROC [dbo].[Incidents] (@SiteName varchar(200)) AS SELECT ( SELECT SUM(i.Logged) FROM tbl_Sites s INNER JOIN tbl_Incidents i ON s.Location = i.Location WHERE s.Sites =…
Icementhols
  • 653
  • 1
  • 9
  • 11
35
votes
12 answers

What is the "??" operator for?

I was wondering about ?? signs in C# code. What is it for? And how can I use it? What about int?? Is it a nullable int? See also: ?? Null Coalescing Operator —> What does coalescing mean?
Ante
  • 8,567
  • 17
  • 58
  • 70
1
2 3
…
62 63