Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL NULL Values
In SQL, NULL represents the absence of a value or an unknown value. It is a special marker used to indicate that a data value does not exist in the database.
Understanding NULL Values
- NULL vs. Empty String: NULL is different from an empty string (”) or a zero (0). While NULL means “no value,” an empty string or zero is a value.
- NULL in Calculations: Any arithmetic operation involving NULL results in NULL. For example, NULL + 10 is NULL.
- NULL Comparisons: NULL cannot be compared using standard comparison operators (=, <, >, etc.). Instead, you use IS NULL or IS NOT NULL.
Checking for NULL Values
Syntax:
SELECT column1, column2, …
FROM table_name
WHERE column IS NULL;
Example:
Retrieve employees who do not have a recorded salary:
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary IS NULL;
Output:
employee_id | first_name | last_name | salary |
12 | Meera | Patel | NULL |
Handling NULL Values in Queries
Using IS NULL and IS NOT NULL:
- IS NULL: Checks if a column has a NULL value.
- IS NOT NULL: Checks if a column has a non-NULL value.
Example:
Retrieve employees who have a recorded salary:
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary IS NOT NULL;
Output:
employee_id | first_name | last_name | salary |
1 | Rajesh | Singh | 65000 |
2 | Aarti | Mehta | 40000 |
3 | Priya | Verma | 70000 |
Using COALESCE to Handle NULL Values
COALESCE is a function that returns the first non-NULL value from a list of expressions. It is useful for providing default values in the presence of NULL.
Syntax:
COALESCE(expression1, expression2, …, default_value)
Example:
Retrieve employees with a default salary of 0 for those who have NULL values:
SELECT employee_id, first_name, last_name, COALESCE(salary, 0) AS salary
FROM employees;
Output:
employee_id | first_name | last_name | salary |
1 | Rajesh | Singh | 65000 |
2 | Aarti | Mehta | 40000 |
3 | Priya | Verma | 70000 |
12 | Meera | Patel | 0 |
Tips to Remember
- NULL is Not Zero or Empty: Understand the distinction between NULL, zero, and an empty string.
- Use IS NULL for Comparison: Use IS NULL or IS NOT NULL to check for NULL values instead of standard comparison operators.
- Use COALESCE for Default Values: Employ COALESCE to handle NULL values by providing default values where needed.
- Be Aware of Aggregate Functions: Most aggregate functions (e.g., SUM, COUNT) ignore NULL values. Be cautious of how NULL affects aggregate calculations.
Power of NULL Handling
Handling NULL values allows you to:
- Ensure Data Integrity: Properly manage and interpret missing data.
- Provide Default Values: Use functions like COALESCE to handle NULL values gracefully.
Avoid Errors: Avoid errors in calculations and queries involving NULL values.
Frequently Asked Questions
Q1: How can I find rows with missing values in multiple columns?
A1: Use OR in the WHERE clause to check multiple columns for NULL values.
Example:
SELECT employee_id, first_name, last_name
FROM employees
WHERE salary IS NULL OR age IS NULL;
Q2: Can NULL be used in aggregate functions?
A2: Yes, but NULL values are generally ignored in aggregate functions like SUM, COUNT, and AVG.
Q3: How does NULL affect sorting?
A3: By default, NULL values are sorted at the end when using ORDER BY. You can use NULLS FIRST or NULLS LAST for custom sorting.
Q4: How can I replace NULL with a meaningful value in a query?
A4: Use the COALESCE function or IFNULL function (in some SQL variants) to replace NULL with a default value.
Example:
SELECT employee_id, first_name, last_name, IFNULL(salary, 0) AS salary
FROM employees;
Q5: Can I insert NULL values into a column?
A5: Yes, if the column allows NULL values. Check column constraints to ensure NULL values are permitted.
