Stack Overflow
Stack Overflow

Questions tagged [ifnull]

This tag refers to the process of determining whether or not a particular object is null, and then running certain code based off of that condition.

Use this tag for questions related to running certain code depending on whether or not one or more objects is null.

161 questions
62
votes
4 answers

MySQL IFNULL ELSE

I have a select statement where I want to make the select conditional like this: IFNULL(field_a, field_a, field_b) so that it checks field a. If a is null then the select would be field b. Is that possible ?
mcgrailm
  • 17,469
  • 22
  • 83
  • 129
49
votes
4 answers

sqlite IFNULL() in postgres

What is the equivalent of SQLite's IFNULL() in Postgres? I have to following query (sqlite in Ruby): SELECT ifnull(max(code_id) + 1, 1) FROM configentries WHERE configtable_id = ... How should this look like if I want the same result with…
nintschger
  • 1,786
  • 5
  • 30
  • 45
23
votes
3 answers

In MySQL, which is more efficient: IFNULL or NULLIF?

These two MySQL functions do the same thing: IFNULL(column_name, 'test') = 'test' or NULLIF(column_name, 'test') IS NULL Which one is more efficient?
user2259597
  • 249
  • 1
  • 2
  • 6
18
votes
2 answers

MySQL IFNULL question

I have a query which contains a subquery which is something like this IFNULL((SELECT id FROM mytable WHERE id = 1), 'No') It works OK, but I would like to return the string 'Yes' on success rather than the actual id of the row. Is there any way to…
Dan
  • 4,068
  • 10
  • 41
  • 51
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
14
votes
2 answers

symfony2 doctrine select IFNULL

Ok i have this code: SELECT IFNULL(s2.id,s1.id) AS effectiveID, IFNULL(s2.status, s1.status) AS effectiveStatus, IFNULL(s2.user_id, s1.user_id) as effectiveUser, IFNULL(s2.likes_count, s1.likes_count) as effectiveLikesCount FROM statuses AS…
Lukas Lukac
  • 7,766
  • 10
  • 65
  • 75
13
votes
4 answers

Replace NULL with SUBTOTAL and TOTAL in a ROLLUP

I have tried to use IFNULL to replace the NULL fields returned by ROLLUP for subtotals and totals but it doesn't appear to be working. Query: select IFNULL(usergroups.name, 'GROUP') AS DEALER, IFNULL(users.name, 'TOTAL') AS…
the_gimlet
  • 183
  • 1
  • 2
  • 7
10
votes
4 answers

How does MySQL CONCAT IFNULL work with more than two values?

I need to CONCAT two fields, if they both exist and add parentheses around the second field. Otherwise I just need to list the first field. Here's the simple version: SELECT id, CONCAT(name,' (',nickname,')') as name FROM user; As long as name and…
Ryan
  • 14,682
  • 32
  • 106
  • 179
6
votes
6 answers

Using IFNULL to set NULLs to zero

I have a table in which a field contains an integer or NULL. parent_id 2 4 6 NULL NULL 45 2 How would I go about adding an IFNULL statement so that ans_count will be populated with 0 instead of NULL? Here is my SQL code: ... (SELECT parent_id AS…
pepe
  • 9,799
  • 25
  • 110
  • 188
5
votes
3 answers

SQL IFNULL on SELECT * statement

Is there a way to set a default value for all returned values that are null, specifically in MySql? Instead of SELECT IFNULL(foo, 0), IFNULL(bar, 0), IFNULL(baz, 0) FROM table I would like to do something like: SELECT IFNULL(*, 0) FROM…
sabrams
  • 1,128
  • 2
  • 15
  • 28
5
votes
1 answer

MySQL check if subquery return NULL, then replace value

I have mysql query where I need to replace value in WHERE clause if subquery returns no results or null value. Price query which works as required Runs Successfully SELECT `prices` FROM `pricing` WHERE (3 BETWEEN `from_unit` AND `to_unit`) AND…
Abhishek Madhani
  • 1,155
  • 4
  • 16
  • 26
4
votes
5 answers

MySQL - count row occurrences in a date range, but convert null to 0 so it shows

This is my current query SELECT DAYNAME(date_created) AS Day, COUNT(*) AS my_count FROM sometable WHERE (@date_created >= '2010-10-20 21:02:38' OR @date_created IS NULL) AND (@date_created <= '2010-10-27 21:02:38' OR @date_created IS NULL) GROUP…
Doug
  • 61
  • 3
4
votes
2 answers

SQL Query for joining non existent rows with a NULL field

I have 2 tables A. LinkNames LINKID NAME 1 Name1 2 Name2 3 Name3 B. Links WebsiteID LINKID LINKPATH 1 1 "WWW.test.com" I need a query that displays output like this LINKID LINKNAME LINKPATH 1 NAME1 …
appdevfan
  • 53
  • 5
4
votes
4 answers

MySql multiple where conditions - only match first correct condition

With data similar to the table below: | id | e_date | e_time | place | person| ref |ref_type| | 10 | 2015-08-03 | 10:30 | work | tony | 1234 | A | | 25 | 2015-08-03 | 10:30 | work | NULL | NULL | A | | 37 | …
goredwards
  • 2,486
  • 2
  • 30
  • 40
4
votes
3 answers

How can i use IFNULL in Where clause

I want to use IFNULL() in such a way that I can select the record containing NULL or, if a value is present, then select the record matchinga particular value. My query is: SELECT (@v:=2) AS Row, CL.LeaveTypeId, CL.NumberOfLeave FROM `CompanyLeave`…
Manish Kumar
  • 595
  • 2
  • 5
  • 20
1
2 3
…
10 11