The problem arise when there are no data for books in specific library. Consider a following working scenario. Table library

--------------------------------
| id |    name     |    owner  |
--------------------------------
|  1 |     ABC     |     A     |
|  2 |     DEF     |     D     |
|  3 |     GHI     |     G     |
--------------------------------

Table books

--------------------------------
| id |    title    |  library  |
--------------------------------
|  a |     xxx     |     1     |
|  b |     yyy     |     1     |
|  c |     zzz     |     2     |
--------------------------------

Now when I do query like below:

SELECT library.name, array_agg(b.title) AS book_list FROM library, 
(SELECT title FROM books WHERE books.library = :library_no) as b 
WHERE library.id = :library_no GROUP BY library.id

The query generates output for library 1 & 2, but not for library 3. Why and how to solve this issue? (Generate an empty list on no library books) Required Output:

----------------------
| name |    book_list |
----------------------
|  GHI |      {}      |   # or {null}
-----------------------

I've even tried coalesce as below:

SELECT library.name, coalesce(array_agg(b.title), ARRAY[]::VARCHAR[]) AS book_list FROM library, 
(SELECT title FROM books WHERE books.library = :library_no) as b 
WHERE library.id = :library_no GROUP BY library.id

Postgres version: 12

[The docs](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver16#comparing-coalesce-and-isnull) insist that ISNULL is a function, but COALESCE is not. Specifically, they say > The ISNULL function and the COALESCE expression If I put on my Lisp hat, I can think of COALESCE as a macro and everything makes sense. However, I've never needed such thinking in T-SQL. So, I must ask. In T-SQL: 1. What is the definition of a function? 2. What is the definition of an expression? 3. How can I tell the difference between a function and an expression?

This is but one of countless little details that make me frustrated all the time, forces me to memorize all kinds of special code and causes my queries to become uglier than they should have to be. Try this query on for size: SELECT sum(amount) FROM table WHERE conditions; If it finds no records to sum() the amount of, it returns empty/null/undefined instead of 0, causing the output to be unexpectedly empty in my application, instead of the "sum" of zero. But sum() means "the sum", so why not just return 0? I am aware of the solution. You "simply" do: SELECT COALESCE(sum(amount), 0) FROM table WHERE conditions; Now it will return 0 even if there are no records. But it's ugly and it no longer feels "fun" to use. Not that databases are supposed to be a "fun game", but you know what I mean: if a query becomes too convoluted/"ugly", it no longer feels satisfying to use it, especially if you know that this will have to be repeated in all kinds of places and it's not just some obscure, one-off edge case. What was the thought process behind making it behave like this? I have many other issues related to null, but I'll focus on this one thing for this question.

I have the following query where I search 2 tables for the item price select productSerial, productName coalesce( (select top 1 price from productPrice pp where pp.id = wi.id order by pp.date), (select top 1 price from productPriceEast ppe where ppe.id = wi.id order by ppe.date) ) as baseprice from warehouseItems wi but now I want the description too, how to change the query to get more than 1 column? I cannot change the query to this since the coalesce would fail. I was thinking of select those queries in the coalesce into temp tables and do some sort of join instead of coalesce not sure if that is correct approach. select top 1 price, productDescrip from productPriceEast where ppe.id = wi.id order by ppe.date

A simple function that looks like this:

CREATE OR REPLACE FUNCTION text_to_tsvector(config regconfig, source text)
 RETURNS tsvector
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE STRICT
RETURN
 to_tsvector(config, coalesce(regexp_replace(source, '\W', ' ', 'g'), ''));

And an even simpler query:

select
	text_to_tsvector('english', '123'),
    text_to_tsvector('english', ''),
    to_tsvector('english', coalesce(regexp_replace(null, '\W', ' ', 'g'), '')),
    text_to_tsvector('english', null)

Results in this: | text_to_tsvector | text_to_tsvector | to_tsvector | text_to_tsvector| | ---------------- | ---------------- | ----------- | ------------| | '123':1 | | | null | Take a note on the last column. [A fiddle is here](https://www.db-fiddle.com/f/b4qwysK8pUwwwckZawKXNX/3) What am I missing?

I have a table with redundant values in the first two columns: ~~~ sqlite> select * from amino where "lebensmittel" = 'Walnüsse'; Lebensmittel Protein pro 100g Histidin Isoleucin Leucin Lysin Methionin Cystein Phenylalanin Tyrosin Threonin Tryptophan Valin ------------ ---------------- -------- --------- ------- ------- --------- ------- ------------ ------- -------- ---------- ------ Walnüsse 15,23 g 391 mg Walnüsse 15,23 g 625 mg Walnüsse 15,23 g 1170 mg Walnüsse 15,23 g 0,424 g Walnüsse 15,23 g 236 mg Walnüsse 15,23 g 208 mg Walnüsse 15,23 g 711 mg Walnüsse 15,23 g 406 mg Walnüsse 15,23 g 596 mg Walnüsse 15,23 g 170 mg Walnüsse 15,23 g 753 mg ~~~ I tried to merge the columns with coalesce, but it works only for the first column: ~~~ sqlite> select "Lebensmittel", "Protein pro 100g", coalesce("Histidin", null) as "Histidin", coalesce("Isoleucin", null) as "Isoleucin", coalesce("Leucin", null) as "Leucin", coalesce("Lysin", null) as "Lysin", coalesce("Methionin", null) as "Methionin", coalesce("Cystein", null) as "Cystein", coalesce("Phenylalanin", null) as "Phenylalanin", coalesce("Tyrosin", null) as "Tyrosin", coalesce("Threonin", null) as "Threonin", coalesce("Tryptophan", null) as "Tryptophan", coalesce("Valin", null) as "Valin" from amino where Lebensmittel = 'Walnüsse' group by "Lebensmittel", "Protein pro 100g"; Lebensmittel Protein pro 100g Histidin Isoleucin Leucin Lysin Methionin Cystein Phenylalanin Tyrosin Threonin Tryptophan Valin ------------ ---------------- -------- --------- ------ ----- --------- ------- ------------ ------- -------- ---------- ----- Walnüsse 15,23 g 391 mg ~~~ Can anybody explain what is wrong with my query?

I have the following demo data (taken from Adventureworks) SalesOrderNumber SalesOrderDetailID SO43659 12 SO43659 11 SO43659 8 SO43659 9 SO43660 14 SO43660 13 SO43661 27 Is it possible to Group per SalesOrderNumber, and show the Detail IDs per SalesOrderNumber? SalesOrderNumber SalesOrderDetailID SO43659 12, 11, 8, 9 SO43660 14, 13 SO43661 27 http://www.sqlfiddle.com/#!3/7708b/1 What would be the best option for this please? Pivots? Cursors? Cursor which calls stored proc for each row , e.g. here ? Or this option from 4guysfromrolla which uses user-defined function? This is something that will be run once on only - at night - I dont need efficiency - just any help please.

I have the following query SELECT SQL_NO_CACHE table1.* FROM table1 LEFT JOIN table2 ON table2.id = table1.table2_id WHERE (table2.date_assigned >= '2015-06-21') AND ( attempt_1_dateIS NOT NULL OR attempt_2_dateIS NOT NULL OR attempt_3_dateIS NOT NULL ) AND ( attempt_1_date >= '2015-08-16' OR attempt_2_date >= '2015-08-16' OR attempt_3_date >= '2015-08-16' ) AND ( callback_dateIS NULL AND callback_by_account_idIS NULL AND callback_resultIS NULL ) AND ( ( attempt_1_result NOT IN ('complete,incorrect,decline,prospecting') OR attempt_1_result IS NULL ) AND ( attempt_2_result NOT IN ('complete,incorrect,decline,prospecting') OR attempt_2_result IS NULL ) AND ( attempt_3_result NOT IN ('complete,incorrect,decline,prospecting') OR attempt_3_result IS NULL ) ) #AND (table2.date_completed IS NULL) AND (COALESCE(table2.date_completed, '') = '') If I coalesce the date_completed field first, my Database program says the query comes back in 0.000 seconds, yet if I used the above (commented out) line, just checking IS NULL, it takes just over 10 seconds. Both only return the same 5 results. table1 has 24 columns, and ~171,000 rows, and table2 has 172 columns*, and 1.7 million rows. In 1.4 million of those rows, date_completed is null. If you need any more information, just let me know. Few more details I was advised to include: MySQL version: 5.5.41-0ubuntu0.14.04.1-log Explain outputs: **Using COALESCE** id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'table1', 'index_merge', 'table2_id,attempt_1_date,attempt_1_result,attempt_2_date,attempt_2_result,attempt_3_date,attempt_3_result', 'attempt_1_date,attempt_2_date,attempt_3_date', '9,9,9', NULL, 8, 'Using sort_union(attempt_1_date,attempt_2_date,attempt_3_date); Using where' 1, 'SIMPLE', 'table2', 'eq_ref', 'PRIMARY,ind_table2_date_assigned', 'PRIMARY', '4', 'database.table1.table2_id', 1, 'Using where' **Using IS NULL** id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'table2', 'ref', 'PRIMARY,ind_table2_date_completed,ind_table2_date_assigned', 'ind_table2_date_completed', '9', 'const', 10, 'Using where' 1, 'SIMPLE', 'table1', 'ref', 'table2_id,attempt_1_date,attempt_1_result,attempt_2_date,attempt_2_result,attempt_3_date,attempt_3_result', 'table2_id', '5', 'database.table2.id', 1, 'Using where' Few more notes. * date_completed is type DATETIME * There are no entries where date_completed = '' *120 of those rows relate to 60 questions, in the format question_x_score, and question_x_value

I am trying to count the number of columns that is retrieved from the subquery, and some subqueries may be empty. So instead of displaying it as an empty cell, I want it to be 0. Found out that I can do this by using the COALESCE function, but for example this query still gives me an empty cell: CREATE VIEW third_table AS SELECT * FROM (second_table NATURAL FULL JOIN ( SELECT assignment_id, COALESCE( count(*), 0 ) AS num_60_79 FROM ( SELECT assignment_id, mark_as_percent FROM avg_required_table WHERE mark_as_percent >= 60 AND mark_as_percent < 80 ) a GROUP BY assignment_id ) b); Is this how COALESCE is supposed to be used?

I am using PostgreSQL as my database. And I need to create an entry in the database, and if it's already exists, just update its fields, but one of the fields should be updated only if it's not set. I've used info from this question: https://stackoverflow.com/questions/13305878/dont-update-column-if-update-value-is-null , it's quite related to what I have. I tried to use this query, but when I run it, it errors with Column reference 'affiliate_code' is ambiguous: INSERT INTO accounts (id, token, affiliate_code) VALUES (value1, value2, value3) ON CONFLICT (id) DO UPDATE SET token = value2, affiliate_code = COALESCE(affiliate_code, value3); (the real values are substituted, of course). If I replace affiliate_code = COALESCE(affiliate_code, value3) with affiliate_code = value3, everything works, but not in the way I want it to work. How can I make this work? Here is how my table is defined: CREATE TABLE accounts ( id VARCHAR NOT NULL UNIQUE, token VARCHAR NOT NULL, affiliate_code VARCHAR );

UPDATE #Product_Comments SET #Product_Comments.Comments = ( SELECT COALESCE(STRING_AGG(p.Label, ', '), '') + ' ' from ( select distinct Label, Product_ID from #TEMPORARYTB where Product_ID = pc.Product_id ) as p GROUP BY p.Product_ID ) + pc.Comments from #Product_Comments pc JOIN Product_Property p ON p.Product_ID = pc.Product_id I used COALESCE() when there was just STRING_AGG(p.Label, ', ') before, and I am still getting NULL inside comments. If I print Product_Comments before the update, I don't see any NULL inside comments. SELECT *, '4' AS debug FROM #Product_Comments I am printing it like this. So, when I do SELECT *, '3' AS debug FROM #Product_Comments I don't see any NULL, which is weird. It means that the COALESCE is not working somehow. How can I fix this?

I have strange behaviour of COALESCE function of PostgreSQL. Depending on arguments of this function the result is different:

SELECT
    "CreatedOn", -- 7/24/2020 8:15:03 AM
    COALESCE("O"."CreatedOn", NOW()) "coalesce_with_now", -- 7/24/2020 11:15:03 AM
    COALESCE("O"."CreatedOn", TO_TIMESTAMP('2020-10-12 10:05:05.260', 'YYYY-MM-DD HH24:MI:SS.FF3')) "coalesce_with_const", -- 7/24/2020 11:15:03 AM
    COALESCE("O"."CreatedOn", NOW() at time zone 'utc') "coalesce_with_now_at_utc", -- 7/24/2020 8:15:03 AM
    COALESCE("O"."CreatedOn") "only_coalesce", -- 7/24/2020 8:15:03 AM
    CASE WHEN "O"."CreatedOn" IS NULL THEN NOW() ELSE "O"."CreatedOn" END "case_analogue", -- 7/24/2020 11:15:03 AM
    CASE WHEN "O"."CreatedOn" IS NOT NULL THEN "O"."CreatedOn" ELSE NOW() END "case_analogue2" -- 7/24/2020 11:15:03 AM
FROM "Opportunity" "O";

Original value is stored in utc. Server has timezone +3. So it changes timezone if any other values are presenting in its arguments. I read documentation of COALESCE method and found no information about such behaviour of this function. Version of PostgreSQL server: PostgreSQL 11.9 (Debian 11.9-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit On other instances (with newer version) I can't reproduce this case. Is it just a bug or this function depends on some server configuration params?

## Situation I'm making a SQL server query to filter data from the projects table _(tblProjecten)_. This table got a lot of many to many relationships to other tables like region _(tblProjectenRegio)_, organisation _(tblProjectenOrganisatie)_, etc. All the parameters I declare are optional. I've found to use coalesce to do this. It works fine if I don't use the joins inside my query here you got my query I've use first: declare @themaid int = 1 ; -- themeID declare @trefwoord nvarchar(max) = '' ; -- search query select distinct p.* from tblProjecten p left join tblProjectenThema pt on p.projectId = pt.projectId where pt.themaId = coalesce(@themaid, pt.themaId) and p.naam like '%' + @trefwoord + '%' ; Below my results for the different declarations _(see also data below)_: @themaid | @trefwoord | results | correct ------| --------| -------------| ------- 1 | | 166 | OK 1 | airco | 166 | OK null | airco | 166 | OK null | | 166, 185 , 415 | OK This works fine but If I add other conditional parameters like query below. I got totally other results. declare @themaid int = 1 ; declare @studiegebiedid int = null ; declare @opleidingdtypeid int = null ; declare @doelgroepid int = null ; declare @organisatorid int = null ; declare @regioid int = null ; declare @trefwoord nvarchar(max) = '' ; select distinct p.* from tblProjecten p left join tblProjectenThema pt on p.projectId = pt.projectId left join tblProjectenStudiegebieden ps on p.projectId = ps.projectid left join tblProjectenOpleidingsType pot on p.projectId = pot.projectID left join tblProjectendoelgroep pd on p.projectId = pd.projectId left join tblProjectenOrganisator po on p.projectId = po.projectId left join tblProjectenRegio pr on p.projectId = pr.projectId where pt.themaId = coalesce(@themaid, pt.themaId) and ps.studiegebiedid = coalesce(@studiegebiedid, ps.studiegebiedid) and pot.opleidingsID = coalesce(@opleidingdtypeid, pot.opleidingsID) and pd.doelgroepId = coalesce(@doelgroepid, pd.doelgroepid) and po.organisatorId = coalesce(@organisatorid, po.organisatorId) and pr.regioId = coalesce(@regioid, pr.regioId) and p.naam like '%' + @trefwoord + '%' ; Here are the results _(the other declarations are null)_: @themaid | @trefwoord | results | correct | must be ---------|------------ |-------------| --------------| ---------- 1 | | | NOT OK | 166 1 | airco | | NOT OK | 166 null | airco | | NOT OK | 166 null | | | NOT OK | 166, 185, 415 This comes because the other tables haven't any data inside it. _(see data below)_ ------------ ## Question My question is now can I ignore the joins without any result to make the last query working? I've also tries to use inner and right joins but give the same results. ------------ ## Data Here you got some data: **tblProjecten:** projectId | naam -----------| ---------- 166 | Attestering AIRCO PROJECT 185 | Autoweb E-LEARNING 415 | Bouw en Hout **tblProjectenThema:** themaId | projectId --------|---------- 1 | 166 2 | 166 2 | 415 3 | 415 6 | 185 **tblProjectendoelgroep:** doelgroepId | projectId ----------- | ----------

I'm trying a SQL query with the function COALESCE in PostgreSQL 9.6.17, but it produces an error: > invalid syntax for type double precision: "" Example query: SELECT COALESCE(date_part('year', s.date_pp),'') FROM public.sendoc s. This query is working on PostgreSQL 9.3.17. How to get around the error at the server DB level without changing the source code of the program?

I am attempting a query to compare the sum of daily accounting invoices to a daily budget. The budget is stored as one value for a month, so I divide that amount by the total number of days as in April would have 30 entries. However, I want the budget amount over days to exist permanently even if there are no invoices for that day, either yet or actually on that day. My current query joins on the date_part('day',a.date) of a timestamp which only hits if there are invoices on that day. I am using PostgreSQL version 11.3. I have tried all join possibilities, and end up with the same result each time. Unless I use CROSS JOIN which does give all 30 days, but the sum of ALL invoices against each day. I had thought that a FULL OUTER JOIN would work, or some other way of using UNION ALL, but as yet I am unsuccessful. WITH RECURSIVE t(days,budget) AS ( SELECT 1 as days,b.amount/30 AS budget FROM budgets b WHERE b.id = 10 UNION ALL SELECT t.days + 1,t.budget FROM t WHERE t.days = '2020-04-01' AND a.date <= '2020-04-30' AND a.account_id = 20 GROUP BY 1,3; For the ouput, lets assume days 1, 2, and 3 have passed where day 2 had no entries. Current Output Day Invoices Budget 1 100 50 3 75 50 Desired Output Day Invoices Budget 1 100 50 2 0 50 3 75 50 4 0 50 5 0 50 ... 30 0 50 I am open to other query ideas to accomplish this. I appreciate the help!

In Oracle 12c (12.1) we have a huge partitioned table with interval partitioning. Beside local indexes, there are two global indexes on it. Dropping old partitions with UPDATE INDEXES clause helps the global indexes keeping valid. Next night, the feature called [Asynchronous Global Index Maintenance](https://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm#VLDBG14107) background job has started an ALTER INDEX xxx COALESCE CLEANUP command, which runs since several days and cleans up the global indexes from orphan entries of dropped partitions. Unfortunately, we need to create another index on this table with different parameters, but it's not possible due to resource busy error because of a shared DML lock (Row-X (SX)) caused by the background job. Since we need to deploy a new release, we need definitely the DDL on another index. For me it looks strange, that alter index ... coalesce cleanup doesn't allow to create another index in parallel. Why should there be any relation? **Question:** If we kill the session, will the work of past 2 days be lost and job need to restart again from beginning?

I'm using PostgreSQL V9.6.11 Table DDL:

CREATE TABLE test_c ( 
          insrt_prcs_id bigint NOT NULL, 
          updt_prcs_id bigint, src_sys_id integer NOT NULL,
          load_dttm timestamp(6) with time zone NOT NULL, 
          updt_dttm timestamp(6) without time zone);

I was trying to create an index for the query below:

SELECT * 
FROM test_c 			
WHERE COALESCE(u_dttm,l_dttm) > '2020-04-10 15:29:44.596311-07'
AND   COALESCE(u_dttm,l_dttm)  '2020-04-10 15:29:44.596311-07'
AND   COALESCE(u_dttm,l_dttm)  '2020-04-10 15:29:44.596311-07'::timestamp with time zone) AND (COALESCE((test_c.updt_dttm)::timestamp with time zone, test_c.load_dttm) <= '2020-04-11 15:29:44.596311-07'::timestamp with time zone))
Rows Removed by Filter: 41304

Why is the index scan not happening?

I made this query: SELECT COALESCE( (SELECT * FROM JournalEntryTitleAndContent jetc WHERE jetc.language_id = 2 AND jetc.journalentry_id = 1 LIMIT 1), (SELECT * FROM JournalEntryTitleAndContent jetc WHERE jetc.language_id = 1 AND jetc.journalentry_id = 1 LIMIT 1), (SELECT * FROM JournalEntryTitleAndContent jetc WHERE jetc.journalentry_id = 1 LIMIT 1) ) Its purpose is to check if there is a row present with language_id 2 (thats the preferred language). If that is not present then the coalesce should try to find the row with the fallbacklanguage with id 1, if that returns null as well then it should return the first row it can find where journalentry_id = 1. When I execute this statement in workbench i get Error Code 1241: Operand should contain 1 column(s) Can i not return a row as a single value from coalesce? If not what should i use instead then? Should i use CASE or IFNULL() instead? Thank you

Running MySQL 8.0.16, settings left at defaults as far as I know. I have a table, let's call it the_table, with columns id: bigint primary key auto_increment the_time: datetime I have a record with the_time of 2019-12-19 00:00:00. When I run either of SELECT id, the_time FROM the_table WHERE the_time BETWEEN '2019-12-19' AND '2019-12-19'; SELECT id, the_time FROM the_table WHERE COALESCE(the_time, DATE('9999-1-1')) BETWEEN '2019-12-19' AND '2019-12-19'; I get that record. However, with SELECT id, the_time FROM the_table WHERE COALESCE(the_time, '9999-1-1') BETWEEN '2019-12-19' AND '2019-12-19'; I do not, though changing it to SELECT id, the_time FROM the_table WHERE COALESCE(the_time, '9999-1-1') BETWEEN '2019-12-18' AND '2019-12-20'; I suddenly do again. Why does MySQL behave this way? Why does the extra DATE() make a difference? I would have thought converting between date and string formats would be bijective and shouldn't affect the computation, but it seems it does. And why does just the coalesce make a difference in the not-null record being found or not?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: SELECT COALESCE(c.FirstName ,(SELECT TOP 1 b.FirstName FROM TableA a JOIN TableB b ON .....) ) If, for example, c.FirstName = 'John', would SQL Server still run the sub-query? I know with the VB.NET IIF() function, if the second argument is True, the code still reads the third argument (even though it won't be used).