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
SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions
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...
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.
Videos
11:54
ISNULL AND NULLIF IN SQL | Advanced SQL | Ashutosh Kumar - YouTube
07:01
SQL | Null Function in SQL | ISNULL | COALESCE | IFNULL | NVL - ...
02:47
SQL IFNULL Function | How to return an alternative value if Column ...
05:11
SQL NULL Functions Explained: COALESCE vs IFNULL for Beginners ...
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/
Databricks
docs.databricks.com › reference › sql language reference › functions › built-in functions › alphabetical list of built-in functions › ifnull function
ifnull function | Databricks on AWS
Applies to: Databricks SQL Databricks Runtime · Returns expr2 if expr1 is NULL, or expr1 otherwise. This function is a synonym for coalesce(expr1, expr2) with two arguments. ifnull(expr1, expr2) expr1: An expression of any type. expr2: An ...
W3Schools
w3schools.com › sql › sql_null_values.asp
SQL NULL Values - IS NULL and IS NOT NULL
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...
StrataScratch
stratascratch.com › blog › introduction-to-ifnull-function-in-sql
Introduction to IFNULL() Function in SQL - StrataScratch
If NULL, the second expression is evaluated, and so on, until a NON-NULL value is found. In this example, we apply the SQL IFNULL() function to a hard-level interview question asked by Google.
W3Schools
w3schools.com › mysql › mysql_ifnull.asp
MySQL IFNULL() and COALESCE() Functions
The MySQL IFNULL() function lets you return an alternative value if an expression is NULL. ... If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail: [email protected] · If you want to ...
CastorDoc
castordoc.com › how-to › how-to-use-ifnull-in-sql-server
How to use ifnull in SQL Server?
However, if the column contains a non-null value, the function simply returns the original value of the column. Ifnull is a function that performs a conditional evaluation on a specified column in SQL Server.
Study.com
study.com › courses › computer science courses › introduction to sql
SQL: IFNULL Function | Study.com
The IFNULL function takes two arguments. It checks to see if the first argument is not NULL. If it is not NULL, it returns the first argument. Otherwise, it returns the second argument.
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.
Snowflake Documentation
docs.snowflake.com › en › sql-reference › functions › ifnull
IFNULL | Snowflake Documentation
If both expressions are NULL, returns NULL.
MariaDB
mariadb.com › kb › en › ifnull
IFNULL | Server | MariaDB Documentation
SELECT IFNULL(1,0); +-------------+ | IFNULL(1,0) | +-------------+ | 1 | +-------------+ SELECT IFNULL(NULL,10); +-----------------+ | IFNULL(NULL,10) | +-----------------+ | 10 | +-----------------+ SELECT IFNULL(1/0,10); +----------------+ | IFNULL(1/0,10) | +----------------+ | 10.0000 | +----------------+ SELECT IFNULL(1/0,'yes'); +-------------------+ | IFNULL(1/0,'yes') | +-------------------+ | yes | +-------------------+ NULL values · IS NULL operator · IS NOT NULL operator · COALESCE function · NULLIF function · CONNECT data types · This page is licensed: GPLv2, originally from fill_help_tables.sql ·
w3resource
w3resource.com › mysql › control-flow-functions › if-null-function.php
MySQL IFNULL() function - w3resource
The IFNULL() function evaluates the first argument (0). Since 0 is not NULL, the function returns this value, ignoring the second argument (2). Hence, the query returns 0 as the output.
DataCamp
datacamp.com › doc › mysql › mysql-ifnull
MySQL IFNULL Keyword: Usage & Examples
sql SELECT product_name, price, quantity, IFNULL(price * quantity, 0) AS total_value FROM products; This example uses `IFNULL` to ensure that any NULL `price` or `quantity` results in a `total_value` of `0`, preventing calculation errors.
TechOnTheNet
techonthenet.com › sql › is_null.php
SQL: IS NULL Condition
This SQL tutorial explains how to use the SQL IS NULL condition with syntax and examples. The IS NULL condition is used in SQL to test for a NULL value. It returns TRUE if a NULL value is found, otherwise it returns FALSE.
SQLPad
sqlpad.io › tutorial › mastering-sql-a-guide-to-the-ifnull-function
Mastering SQL: A Guide to the IFNULL Function - Dive deep into the SQL IFNULL function with this comprehensive guide. Learn how to use IFNULL to handle NULL values effectively in your database queries. - SQLPad.io
Using IFNULL, one can easily replace these NULLs with a default value, say 'Not Provided', making the dataset cleaner and more consistent. SELECT IFNULL(PhoneNumber, 'Not Provided') AS PhoneNumberCleaned FROM UserProfiles; This simple manipulation ensures that downstream processes, such as data integration or analytics, are not hindered by missing information, enhancing the overall quality and usability of the dataset. For more on data cleaning techniques, explore SQLPad.
IONOS
ionos.com › digital guide › server › configuration › sql ifnull
How to use the SQL IFNULL() function to output alternative values
February 7, 2025 - IFNULL('This is the first expression', 'This is the alternative');sql · When you execute the code, the output looks like this: ... The first expression has a value and therefore the function doesn’t have an alternative. The next example is different: ... Since the value of the first expression is NULL, the function resorts to the alternative.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › queries › is-null-transact-sql
IS NULL (Transact-SQL) - SQL Server | Microsoft Learn
If the value of expression is NULL, IS NOT NULL returns FALSE; otherwise, it returns TRUE. To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return ...
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 AdventureWorks2022. If the maximum quantity for a particular special offer is NULL, the MaxQty shown in the result set is 0.00.
W3Schools
w3schools.com › sql › func_sqlserver_isnull.asp
SQL Server ISNULL() Function
SQL Examples SQL Editor SQL Quiz SQL Exercises SQL Server SQL Syllabus SQL Study Plan SQL Bootcamp SQL Certificate SQL Training ... The ISNULL() function returns a specified value if the expression is NULL.