You can use a CASE statement.
SELECT
CASE WHEN currate.currentrate IS NULL THEN 1 ELSE currate.currentrate END
FROM ...
W3Schools
w3schools.com › sql › sql_isnull.asp
W3Schools.com
String Functions: ASCII CHAR_LENGTH ... BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION SQL Server Functions...
Top answer 1 of 6
145
You can use a CASE statement.
SELECT
CASE WHEN currate.currentrate IS NULL THEN 1 ELSE currate.currentrate END
FROM ...
2 of 6
57
You can use COALESCE:
SELECT orderhed.ordernum,
orderhed.orderdate,
currrate.currencycode,
coalesce(currrate.currentrate, 1) as currentrate
FROM orderhed
LEFT OUTER JOIN currrate
ON orderhed.company = currrate.company
AND orderhed.orderdate = currrate.effectivedate
Or even IsNull():
SELECT orderhed.ordernum,
orderhed.orderdate,
currrate.currencycode,
IsNull(currrate.currentrate, 1) as currentrate
FROM orderhed
LEFT OUTER JOIN currrate
ON orderhed.company = currrate.company
AND orderhed.orderdate = currrate.effectivedate
Here is an article to help decide between COALESCE and IsNull:
http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/
How to return a NULL if condition is met
Select CASE WHEN Status = 5 THEN NULL ELSE [datefield] END as [NameyouWantFieldToBeCalled] From [Table] More on reddit.com
7
10
June 10, 2024DBeaver: select from ODBC shows NULL where there are actual values
There is a fix for this, first go to the "ODBC DataSources" application. Select the DSN in question and click configure. At the bottom click the "data type options" that would open a window called "data type configuration". In the data type configuration window uncheck the options "use unicode" and "show boolean column as text". Then save the changes. Now delete the existing connection in DBeaver and create a new one for the ODBC source. You should not see NULL's anymore. (Please delete existing connection and create a new one, refreshing the connection does not work). More on reddit.com
6
2
April 25, 2022BigQuery: CONCAT ignoring NULL
Coalesce and Concat are ansi so this should work anywhere.
Concat(Coalesce ( thing1, ''), Coalesce (thing2,''))
More on reddit.com 6
1
October 7, 2019pandas to_sql() inserting despite NOT NULL constraint in table schema?
The database controls the constraints of the columns. If it is set up correctly, the db should generate an error. I didn't use pandas but it shouldn't make any difference. Example of when the column has the not null constraint and the insert has a null value c.execute('''CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50) );''') c.execute("INSERT INTO COMPANY('ID', 'NAME', 'AGE', 'ADDRESS')VALUES ('ID', 'IBM', null, 'here');") sqlite3.IntegrityError: NOT NULL constraint failed: COMPANY.AGE More on reddit.com
3
1
April 15, 2020Videos
08:49
Using SQL NULLIF with Missing Values | Essential SQL - YouTube
06:07
SQL SERVER||NULLIF Function in SQL and use case of NULLIF - YouTube
53:00
SQL NULL Functions | COALESCE, ISNULL, NULLIF, IS (NOT) NULL | ...
07:01
SQL | Null Function in SQL | ISNULL | COALESCE | IFNULL | NVL - ...
11:54
ISNULL AND NULLIF IN SQL | Advanced SQL | Ashutosh Kumar - YouTube
SQLServerCentral
sqlservercentral.com › forums › topic › nullif-and-0
NULLIF and 0 – SQLServerCentral Forums
March 25, 2021 - The NULLIF function returns NULL if the 2 arguments are equal, otherwise it just returns the first argument. The first and third expressions are clearly equivalent expressions, so return NULL.
W3Schools
w3schools.com › sql › func_mysql_ifnull.asp
MySQL IFNULL() Function
SQL Examples SQL Editor SQL Quiz SQL Exercises SQL Server SQL Syllabus SQL Study Plan SQL Bootcamp SQL Certificate SQL Training ... The IFNULL() function returns a specified value if the expression is NULL.
W3Schools
w3schools.com › sql › func_sqlserver_nullif.asp
SQL Server NULLIF() Function
SQL Examples SQL Editor SQL Quiz SQL Exercises SQL Server SQL Syllabus SQL Study Plan SQL Bootcamp SQL Certificate SQL Training ... The NULLIF() function returns NULL if two expressions are equal, otherwise it returns the first expression.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › functions › isnull-transact-sql
ISNULL (Transact-SQL) - SQL Server | Microsoft Learn
It substitutes the value 50 for all NULL entries in the Weight column of the Product table. USE AdventureWorks2022; GO SELECT AVG(ISNULL(Weight, 50)) FROM Production.Product; GO · Here's the result set. ... The following example selects the description, discount percentage, minimum quantity, and maximum quantity for all special offers in AdventureWorks2025. If the maximum quantity for a particular special offer is NULL, the MaxQty shown in the result set is 0.00.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › language-elements › nullif-transact-sql
NULLIF (Transact-SQL) - SQL Server | Microsoft Learn
If the expressions are equal, NULLIF returns a null value of the type of the first expression.
Reintech
reintech.io › blog › sql-isnull-and-nullif-functions-tutorial
SQL 'ISNULL' & 'NULLIF' Functions | Reintech media
September 29, 2023 - This article provides a detailed ... used to replace NULL values with a specified replacement value, while NULLIF returns NULL if two expressions are equal....
SQL Server Tutorial
sqlservertutorial.net › home › sql server basics › sql server nullif
SQL Server NULLIF
July 10, 2020 - The NULLIF expression accepts two arguments and returns NULL if two arguments are equal. Otherwise, it returns the first expression. The following shows the syntax of the NULLIF expression: NULLIF(expression1, expression2) Code language: SQL ...
Reddit
reddit.com › r/sql › how to return a null if condition is met
r/SQL on Reddit: How to return a NULL if condition is met
June 10, 2024 -
I am working on a query where I need to try and return the date column as Null even if it has a date in it.
The query needs to search 'status' for '5' then returns the column end date as null if it is 5 and leave it if it isn't 5.
I have a tried a few different ideas and haven't managed to get them to work wondering if anyone else might be aware on how to do this?
Top answer 1 of 4
37
Select CASE WHEN Status = 5 THEN NULL ELSE [datefield] END as [NameyouWantFieldToBeCalled] From [Table]
2 of 4
3
ANSI It works everywhere but it’s verbose. CASE status WHEN 5 THEN NULL ELSE EndDate END Nonstandard IF/IFF/IIF Ternary It’s cleaner but no one can agree on how to spell IF IF(status=5, NULL, EndDate) IF MySQL Spark SQL IFF Databricks Snowflake IIF Microsoft Access Microsoft T-SQL
Sqlteam
weblogs.sqlteam.com › jeffs › 2007 › 09 › 27 › sql-nullif-function
A handy but little-known SQL function: NULLIF() | Jeff Smith Blog
October 6, 2010 - NULLIF() returns NULL if the two parameters provided are equal; otherwise, the value of the first parameter is returned. Seems a little odd and not very useful, but it is a great way of ensuring that empty strings are always returned as NULLS. For example, the expression: nullif(override,'') ...
Oracle
docs.oracle.com › en › database › oracle › oracle-database › 26 › sqlrf › NULLIF.html
SQL Language Reference
1 month ago - NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1.
GeeksforGeeks
geeksforgeeks.org › sql › nullif-function-in-sql-server
NULLIF() Function in SQL Server - GeeksforGeeks
July 23, 2025 - NULLIF() function in SQL Server is used to check if the two specified expressions are equal or not. If two arguments passed to a function are equal, the NULLIF() function returns Null to us. NULLIF() function operates like a Like a CASE Statement.
W3Schools
w3schools.com › sql › func_mysql_nullif.asp
MySQL NULLIF() Function
SQL Examples SQL Editor SQL Quiz SQL Exercises SQL Server SQL Syllabus SQL Study Plan SQL Bootcamp SQL Certificate SQL Training ... The NULLIF() function compares two expressions and returns NULL if they are equal.
Medium
medium.com › @etimfonime › can-you-use-nullif-for-multiple-criteria-in-sql-58ab1ec801db
Can You Use NULLIF() Function for Multiple Criteria in SQL? | by Ime Eti-mfon | Medium
July 11, 2024 - Yes, you can use the NULLIF() function for multiple criteria in SQL by nesting the NULLIF() functions or using it in conjunction with other SQL functions and constructs. However, NULLIF() itself only compares two expressions and returns NULL ...
Hightouch
hightouch.com › sql-dictionary › sql-nullif
SQL NULLIF - Syntax, Use Cases, and Examples | Hightouch
December 29, 2023 - The SQL NULLIF function is used to compare two expressions or values and return NULL if they are equal. It provides a way to handle specific cases where you want to treat equality as a special condition by substituting it with a NULL value.
IBM
ibm.com › docs › en › db2-for-zos › 12.0.0
NULLIF scalar function - Db2 SQL
The NULLIF function returns the null value if the two arguments are equal; otherwise, it returns the value of the first argument.
Naukri
naukri.com › code360 › library › sql-nullif
SQL NULLIF - Naukri Code 360
March 27, 2024 - Almost there... just a few more seconds
SQL Tutorial
sqltutorial.org › home › sql nullif function
SQL NULLIF Function
February 8, 2025 - It returns value1 if the values are not equal. The NULLIF function is equivalent to the following searched CASE expression: CASE WHEN value1 = value1 THEN NULL ELSE value1 ENDCode language: SQL (Structured Query Language) (sql)
Salesforce Developers
developer.salesforce.com › docs › atlas.en-us.bi_dev_guide_sql.meta › bi_dev_guide_sql › bi_sql_nullif.htm
NULLIF() | SQL for Analytics Developer Guide | Salesforce Developers
Use the nullif() function as shorthand for a searched case statement where two equal expressions return null.