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
33
votes
5 answers

Return zero if no record is found

I have a query inside a stored procedure that sums some values inside a table: SELECT SUM(columnA) FROM my_table WHERE columnB = 1 INTO res; After this select I subtract res value with an integer retrieved by another query and return the result. If…
giozh
  • 9,868
  • 30
  • 102
  • 183
31
votes
7 answers

combine rows in data frame containing NA to make complete row

I know this is a duplicate Q but I can't seem to find the post again Using the following data df <- data.frame(A=c(1,1,2,2),B=c(NA,2,NA,4),C=c(3,NA,NA,5),D=c(NA,2,3,NA),E=c(5,NA,NA,4)) A B C D E 1 NA 3 NA 5 1 2 NA 2 NA 2 NA NA 3…
CPak
  • 13,260
  • 3
  • 30
  • 48
31
votes
8 answers

Return first non-null value

I have a number of functions: String first(){} String second(){} ... String default(){} Each can return a null value, except the default. each function can take different parameters. For example, first could take no arguments, second could take in…
lorenzocastillo
  • 985
  • 3
  • 13
  • 24
29
votes
3 answers

Does Twig have a null coalesce operator?

I'm using the Twig PHP template engine. Is there an operator available which will output first non-empty value (coalesce)? For example (using PHP pseudocode): {{ title ?: "Default Title" }} I know I could do something like this, but it's a bit…
Adrian Macneil
  • 13,017
  • 5
  • 57
  • 70
28
votes
2 answers

Is there a function equivalent to the Oracle's NVL in MySQL?

I'm selecting the max of a column from a table. But there is one problem: if there are no rows in the table, it returns null. I want to use a function which will return a certain value if the result is null. For example with Oracle there is the NVL…
user833129
27
votes
3 answers

Why is T-SQL ISNULL() truncating the string and COALESCE is not?

Given the following: SELECT ISNULL('XY' + NULL, 'ABCDEFGHIJ') -- Outputs ABC (Why?) SELECT COALESCE('XY' + NULL, 'ABCDEFGHIJ') -- Outputs ABCDEFGHIJ Why are these statements returning different results?
natenho
  • 5,231
  • 4
  • 27
  • 52
24
votes
4 answers

Is the null coalesce operator thread safe?

So this is the meat of the question: Can Foo.Bar ever return null? To clarify, can '_bar' be set to null after it's evaluated as non-null and before it's value is returned? public class Foo { Object _bar; public Object Bar …
csharptest.net
  • 62,602
  • 11
  • 71
  • 89
22
votes
4 answers

COALESCE() for blank (but not null) fields

I have two fields that I'm comparing with MySQL's function COALESCE(). For example, COALESCE(Field1, Field2). The problem is, Field1 is sometimes blank but not null; since it's not null COALESCE() selects Field1, even though its blank. In that case,…
Luke Shaheen
  • 4,262
  • 12
  • 52
  • 82
20
votes
2 answers

ORA-12704: character set mismatch

Hell when I do: select COALESCE (CORP_ID, 0) from crmuser.accounts; The CORP_ID records which are Null returns 0 but when I do: select COALESCE (EMAIL, 'NO EMAIL') from crmuser.accounts I get an error: ORA-12704: character set mismatch The EMAIL…
Stanley Mungai
  • 4,044
  • 30
  • 100
  • 168
17
votes
2 answers

Left outer join - how to return a boolean for existence in the second table?

In PostgreSQL 9 on CentOS 6 there are 60000 records in pref_users table: # \d pref_users Table "public.pref_users" Column | Type | Modifiers …
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
17
votes
3 answers

Using NVL for multiple columns - Oracle SQL

Good morning my beloved sql wizards and sorcerers, I am wanting to substitute on 3 columns of data across 3 tables. Currently I am using the NVL function, however that is restricted to two columns. See below for an example: SELECT ccc.case_id, …
Heisenberg
  • 267
  • 3
  • 6
  • 15
17
votes
2 answers

Merge rows in a dataframe where the rows are disjoint and contain NAs

I have a dataframe that has two rows: | code | name | v1 | v2 | v3 | v4 | |------|-------|----|----|----|----| | 345 | Yemen | NA | 2 | 3 | NA | | 346 | Yemen | 4 | NA | NA | 5 | Is there an easy way to merge these two rows? What if I rename…
LukasKawerau
  • 1,071
  • 2
  • 23
  • 42
16
votes
3 answers

Select a default value if returned result is NULL in MySQL

The following query runs fine, but I want to input the option that if the email returned is NULL, then I input "example@google.com" or something similar. I have read up on a few functions to do so, like COALESCE(EMAIL,"example@google.com"), but I…
user1615559
  • 333
  • 2
  • 8
  • 18
15
votes
1 answer

Spark: coalesce very slow even the output data is very small

I have the following code in Spark: myData.filter(t => t.getMyEnum() == null) .map(t => t.toString) .saveAsTextFile("myOutput") There are 2000+ files in the myOutput folder, but only a few t.getMyEnum() == null, so there are only very…
Edamame
  • 23,718
  • 73
  • 186
  • 320
14
votes
4 answers

MySQL, IFNULL(), COALESCE() on String not replacing

My statement should replace every empty title_column with 'no name', but it doesn't: SELECT COALESCE(main_table.title_column, 'no name') AS title FROM main_table; IFNULL() behaves the same way. What am I doing wrong ?
kiltek
  • 3,183
  • 6
  • 47
  • 70
1
2
3
…
62 63