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

Clojure coalesce function

SQL offers a function called coalesce(a, b, c, ...) that returns null if all of its arguments are null, otherwise it returns the first non-null argument. How would you go about writing something like this in Clojure? It will be called like this:…
Ralph
  • 31,584
  • 38
  • 145
  • 282
13
votes
1 answer

ERROR: COALESCE types timestamp without time zone and integer cannot be matched (Postgresql)

## PROBLEM ## I got error from this script (Postgresql 9.3.2) ( It's OK in MS SQL Server ) SELECT CASE COALESCE(my_date_field,0) WHEN 0 THEN 0 ELSE 1 END AS status FROM my_table Error :COALESCE types timestamp without time zone and…
postgreat
  • 545
  • 3
  • 7
  • 16
12
votes
3 answers

COALESCE - guaranteed to short-circuit?

From this question, a neat answer about using COALESCE to simplify complex logic trees. I considered the problem of short circuiting. For instance, in functions in most languages, arguments are fully evaluated and are then passed into the function.…
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
12
votes
2 answers

Is postgres COALESCE lazy?

If I have a query like this: SELECT COALESCE( (SELECT value FROM precomputed WHERE ...), alwaysComputeValue(...) ); Will the second expression be evaluated? Also can this depends on execution planner or it is independent?
langpavel
  • 1,270
  • 12
  • 16
12
votes
3 answers

COALESCE with NULL

I found this snippet of SQL in a view and I am rather puzzled by it's purpose (actual SQL shortened for brevity): SELECT COALESCE(b.Foo, NULL) AS Foo FROM a LEFT JOIN b ON b.aId=a.Id I cannot think of a single reason of the purpose of…
Colin Burnett
  • 11,150
  • 6
  • 31
  • 40
11
votes
3 answers

ORDER BY with columns that are sometimes empty

My SQL looks something like this: SELECT CompanyName , LastName , FirstName FROM ... JOIN ... ORDER BY CompanyName , LastName , FirstName Now the problem is that column A is sometimes empty (either as NULL or ""), and I don't want all those results…
Kajetan Abt
  • 1,505
  • 3
  • 15
  • 28
11
votes
4 answers

COALESCE, IFNULL, or NZ() function that can be used in SQL Server and MS Access

I have a project that can use either SQL Server or MS Access as the data store. In one SELECT statement, I must perform a COALESCE operation on a single column and a single value, like this: SELECT COALESCE([Amount], 0) FROM PaymentsDue; I would…
Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
11
votes
2 answers

Postgresql COALESCE performance problem

I have this table in Postgresql: CREATE TABLE my_table ( id bigint NOT NULL, value bigint, CONSTRAINT my_table_pkey PRIMARY KEY (id) ); There are ~50000 rows in my_table. The question is, why the query: SELECT * FROM my_table WHERE id =…
Tair
  • 3,779
  • 2
  • 20
  • 33
11
votes
2 answers

Postgres coalesce to empty JSONB array

How can I coalesce a null column into an empty JSONB array? This doesn't work: SELECT jsonb_array_elements(coalesce(null_column, '{}'::jsonb)) FROM table WHERE id = 13; -- ERROR: cannot extract elements from an object Neither this: SELECT…
Sam R.
  • 16,027
  • 12
  • 69
  • 122
11
votes
1 answer

How to collapse many records into one while removing NA values

Say I have the following dataframe df name <- c("Bill", "Rob", "Joe", "Joe") address <- c("123 Main St", "234 Broad St", NA, "456 North Ave") favteam <- c("Dodgers", "Mets", "Pirates", NA) df <- data.frame(name = name, address =…
mcjudd
  • 1,520
  • 2
  • 18
  • 33
11
votes
3 answers

COALESCE in JPA namedQuery

I have the following namedQuery select new test.entity.Emp(COALESCE(k.projectId,'N') as projectId, k.projectName) from Emp o inner join o.projects k However I am getting error expecting RIGHT_ROUND_BRACKET, found '(' How to handle COALESCE in…
Jacob
  • 14,463
  • 65
  • 207
  • 320
10
votes
1 answer

Is there a way to query for a possible non-existent column in mysql?

I have a script where a user can select a field across multiple tables. It uses a Union to get all of the rows. Right now, I have a mapping array to indicate whether a specific field exists in each table, and if it doesn't, it uses "" as…
Anthony
  • 36,459
  • 25
  • 97
  • 163
9
votes
1 answer

How to figure out which column/value the COALESCE operator successfully selected?

I have a table that I wish to find the first non-null value from 3 (and only 3) columns for each ID starting with Col1 then to Col2 then to Col3 Note: Col3 is NEVER NULL ID Col1 Col2 Col3 ------------------------------ 1 A B …
Jon Erickson
  • 112,242
  • 44
  • 136
  • 174
9
votes
4 answers

How to mimick the functionality of the SQL Coalesce statement in Javascript

I was wondering if there is a way in javascript to have logic similar to the coalesce statement in sql which will return data in a specified order like this: Select top 1 Coalesce(ColA, ColB, "No Data Found") from TableA; is there an elegant way to…
some_bloody_fool
  • 4,605
  • 14
  • 37
  • 46
9
votes
1 answer

How to use coalesced memory access

I have 'N' threads to perform simultaneously on device which they need M*N float from the global memory. What is the correct way to access the global memory coalesced? In this matter, how the shared memory can help?
Behzad Baghapour
  • 169
  • 1
  • 7
1 2
3
…
62 63