MySQL | MacLochlainns Weblog

Archive for the ‘MySQL’ Category

SQL Handling Nulls

Interesting questions always come via my students. For example, “Why does the selective aggregation sample return null values as totals from the SUM() function in MySQL?”

First, here’s the code to build the sample table for the problem:

DROP TABLE IF EXISTS transaction;
CREATE TABLE transaction
( transaction_id      int unsigned primary key auto_increment
, transaction_date    date
, transaction_amount  double );
 
INSERT INTO transaction
( transaction_date, transaction_amount )
VALUES
 ('2021-01-10', 56)
,('2021-02-14',23.02)
,('2021-03-31',31.06)
,('2021-01-01',.25)
,('2020-01-02', 52)
,('2020-02-08',22.02)
,('2020-03-26',32.06)
,('2020-01-12',.75);;

Now, here’s the selective aggregation query:

SELECT   EXTRACT(YEAR FROM transaction_date) AS "Year"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
            END) AS "Jan"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN transaction_amount
           END) AS "Feb"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN transaction_amount
           END) AS "Mar"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) IN (1,2,3) THEN transaction_amount
           END) AS "1FQ"
FROM     transaction
WHERE    transaction_date BETWEEN '2020-01-15' AND '2021-03-15'
GROUP BY EXTRACT(YEAR FROM transaction_date)
ORDER BY 1;

It produces the following correct results (at least in response to the query above):

+------+-------+-------+-------+-------+
| Year | Jan   | Feb   | Mar   | 1FQ   |
+------+-------+-------+-------+-------+
| 2020 |  NULL | 22.02 | 32.06 | 54.08 |
| 2021 | 56.25 | 23.02 |  NULL | 79.27 |
+------+-------+-------+-------+-------+
2 rows in set (0.02 sec)

Why do you get null values for January 2020 and March 2021? That’s because the query returns null values when the conditions in the SELECT-list aren’t met for a row return. This happens:

When a row is returned for the month of February or March a null is returned in the January column.
When a row is returned for the month of January or March a null is returned in the February column.
When a row is returned for the month of January or February a null is returned in the March column.

That means there needs to be an ELSE clause in each of the CASE statements that sets the return value to zero. For example, the following query includes the ELSE clause and some nice formatting tricks:

SELECT   EXTRACT(YEAR FROM transaction_date) AS "Year"
,        CONCAT('$',LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
             ELSE 0
            END),2),8,' ')) AS "Jan"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "Feb"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "Mar"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) IN (1,2,3) THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "1FQ"
FROM     transaction
WHERE    transaction_date BETWEEN '2020-01-15' AND '2021-03-15'
GROUP BY EXTRACT(YEAR FROM transaction_date)
ORDER BY 1;

It returns:

+------+-----------+----------+----------+----------+
| Year | Jan       | Feb      | Mar      | 1FQ      |
+------+-----------+----------+----------+----------+
| 2020 | $    0.00 |    22.02 |    32.06 |    54.08 |
| 2021 | $   56.25 |    23.02 |     0.00 |    79.27 |
+------+-----------+----------+----------+----------+
2 rows in set (0.01 sec)

As always, I hope this helps answer a question that somebody is trying to sort out.

Written by maclochlainn

June 23rd, 2021 at 12:23 pm

Posted in MySQL,MySQL 8,MySQL Client,MySQL Workbench

Tagged with MySQL DBA, MySQL Developer

MySQL Outer Joins

without comments

The students needed yet another example of LEFT JOIN, RIGHT JOIN, and FULL JOIN syntax (by combining a left and right join with the UNION set operator). To that end, I put this set of examples together.

The example also shows how to order the result set from a derived table with the UNION operator. It uses the WITH clause to build a Common Table Expression (CTE), which allows the query to order the UNION set operator’s product based on the left and right join queries. It uses a CASE statement to order the result sets. The left_table is the parent table and the right_table is the child table in the relationship, which means the right_table holds a left_id foreign key column that lets you connect matching rows in the left_table.

You build the little model with the following script:

-- -----------------------------------------------------------------
-- Drop the demonstration tables.
-- -----------------------------------------------------------------
DROP TABLE IF EXISTS left_table, right_table;
 
-- -----------------------------------------------------------------
-- Create left_table.
-- -----------------------------------------------------------------
CREATE TABLE left_table
( left_id        int unsigned primary key auto_increment
, leftstring     varchar(10));
 
-- -----------------------------------------------------------------
-- Create left_table.
-- -----------------------------------------------------------------
CREATE TABLE right_table
( right_id       int unsigned primary key auto_increment
, left_id        int unsigned
, rightstring         varchar(10));
 
-- -----------------------------------------------------------------
-- Insert five rows to the left table, which holds a 
-- left_id primary key column.
-- -----------------------------------------------------------------
INSERT INTO left_table (leftstring) values ('One');
INSERT INTO left_table (leftstring) values ('Two');
INSERT INTO left_table (leftstring) values ('Three');
INSERT INTO left_table (leftstring) values ('Four');
INSERT INTO left_table (leftstring) values ('Five');
 
-- -----------------------------------------------------------------
-- Delete row four to create a gap.
-- -----------------------------------------------------------------
DELETE FROM left_table where left_id = 4;
 
-- -----------------------------------------------------------------
--  Insert four rows, skipping a foreign key value for the 
--  left_id primary key value of 2.
-- -----------------------------------------------------------------
 
INSERT INTO right_table (rightstring,left_id) values ('One',1);
INSERT INTO right_table (rightstring,left_id) values ('Three',3);
INSERT INTO right_table (rightstring,left_id) values ('Four',4);
INSERT INTO right_table (rightstring,left_id) values ('Five',5);

-- ----------------------------------------------------------------- -- Drop the demonstration tables. -- ----------------------------------------------------------------- DROP TABLE IF EXISTS left_table, right_table; -- ----------------------------------------------------------------- -- Create left_table. -- ----------------------------------------------------------------- CREATE TABLE left_table ( left_id int unsigned primary key auto_increment , leftstring varchar(10)); -- ----------------------------------------------------------------- -- Create left_table. -- ----------------------------------------------------------------- CREATE TABLE right_table ( right_id int unsigned primary key auto_increment , left_id int unsigned , rightstring varchar(10)); -- ----------------------------------------------------------------- -- Insert five rows to the left table, which holds a -- left_id primary key column. -- ----------------------------------------------------------------- INSERT INTO left_table (leftstring) values ('One'); INSERT INTO left_table (leftstring) values ('Two'); INSERT INTO left_table (leftstring) values ('Three'); INSERT INTO left_table (leftstring) values ('Four'); INSERT INTO left_table (leftstring) values ('Five'); -- ----------------------------------------------------------------- -- Delete row four to create a gap. -- ----------------------------------------------------------------- DELETE FROM left_table where left_id = 4; -- ----------------------------------------------------------------- -- Insert four rows, skipping a foreign key value for the -- left_id primary key value of 2. -- ----------------------------------------------------------------- INSERT INTO right_table (rightstring,left_id) values ('One',1); INSERT INTO right_table (rightstring,left_id) values ('Three',3); INSERT INTO right_table (rightstring,left_id) values ('Four',4); INSERT INTO right_table (rightstring,left_id) values ('Five',5);

Here are the join statements:

INNER JOIN

The INNER JOIN only returns those rows that match between a primary and foreign key column or set of columns.

SELECT l.left_id
,      l.leftstring
,      r.left_id
,      r.right_id
,      r.rightstring
FROM   left_table l INNER JOIN right_table r
ON     l.left_id = r.left_id;