当前位置：网站首页>SQL | null value and SQL count() function

SQL | null value and SQL count() function

2022-07-21 23:49:00 【Navicat China】

As early as 2020 year 3 month , In relational database system NULL Value and its use One article introduces NULL Value and its special meaning in relational database . The article also described how to allow NULL And how to reference them in queries . In today's article , We will learn how to make NULL And SQL Count() Functions are combined to achieve various goals .

Calculation Null Value and non Null value

Count() There are two forms of functions ：COUNT(*) Return all rows in the table , and COUNT(Expression) Ignore Null expression . therefore , If you provide a permission NULL The column name of the value , that Count() Will return with non NULL All rows of values .Count() The two different uses of are how we get a specific column NULL Value counting provides important clues . This is done by subtracting non NULL Number of fields , As shown below ：

SELECT COUNT(*) - COUNT(<Column Name>)

Now we know how to calculate NULL That's ok 、 Not NULL Rows and all rows , Let's take an example . We will be on MySQL classicmodels Sample database Of customers Table to run this query . This is a Navicat Premium Table designer

addressline2 The field contains additional address details that do not belong to the street name and number . therefore , As we can see in this table data example , Not all addresses need it ：

This query is used in three ways Count() Function to display all table rows 、 With data addressLine2 Number of rows and Null Number of value lines ：

SELECT COUNT(*) AS All_Rows,
       COUNT(addressLine2) AS addressLine2_Count, 
       COUNT(*) - COUNT(addressLine2) AS Null_addressLine2_Rows
FROM customers;

This is a Navicat Premium In the query designer of SELECT sentence , And the result ：

As expected ,addressLine2_Count and Null_addressLine2_Rows The result adds up to All_Rows Count .

Use in content analysis NULL

Count() Functional COUNT(Expression) Version ignores Null The fact that expressions are useful for compiling statistics about table data , Especially with SQL IF() Function in combination with other functions , This is basically a ternary operator SQL equivalent ：

IF(predicate, true-value, false-value)

If the predicate is true, be IF The calculation for the true value , Or in the following query 1. If the predicate is false, Then the result is false Value or NULL, As the following statement shows . then COUNT take IF by 1（ The predicate is true） Each row of is tabulated ：

SELECT count(IF(country = 'Australia', 1, NULL)) as Australia_Count, 
       count(IF(country = 'Germany', 1, NULL)) as Germany_Count, 
       count(IF(country = 'Canada' OR country = 'USA', 1, NULL)) as North_America_Count, 
       count(IF(country like 'F%', 1, NULL)) as F_Countries_Count, 
       count(IF(creditLimit between 20000 and 1000000, 1, NULL)) as CreditLimit_Range_Count, 
       count(*) as Total_Count
FROM customers
WHERE dob >= '1960-01-01';

This is a Navicat Queries and results in ：

summary

In today's article , We learned how to make NULL And SQL Count() Functions are combined to achieve various goals . It's not just computation NULL He Fei NULL A way to value , When with others SQL function （ Such as IF() and SUM()） When used in combination , They can be used to compile various statistical information about data ！ If you want to try Navicat 16, Can be in here download Navicat Of 14 Tianquan feature free trial .