What’s missing? What matches in one table but not another? MySQL (and SQL in general) have a way to find out. More than likely, when working with a normalized database, you are faced with these types of questions. Querying multiple tables with JOIN‘s on matched values, only goes so far to answering this type of question. We will see how to leverage an OUTER JOIN, for these requests.
divider
MySQL
INNER JOIN in MySQL with examples.
To normalize data, we often store it in several tables within a given database. While querying those tables individually can provide answers to numerous questions around that data, more times than not, it leaves us ‘coming up short’ for the complete answer we need.
That poses the question of, how do you extract all this data from several tables that depend on one another to provide data-driven answers?
One tried and true method is to use a JOIN operation.
MySQL String Data Type Dive: CHAR and VARCHAR – Differences.
Data types… They are oh so important when designing your database tables. In this blog post, I look at two similar string data types: CHAR and VARCHAR. Chances are you will likely use one, the other, or both in MySQL. But what is the difference in them? I want to know myself. So let’s learn together…
Easy mysqldump backups by example.
This blog post, will present three sample uses of the mysqldump backup utility. It will entail backing up: a database, a table, along with a table without data. This utility can easily be incorporated into shell scripts for those repetitive tasks you may need to execute on multiple occasions.
Aggregate Functions Part 3: MIN() and MAX() in MySQL with examples.
Coming into this third blog post on Aggregate Functions, I will visit 2 powerful functions: MIN() and MAX(). They provide a broad range of functionality you can leverage. What sets them apart, is their ability to operate on multiple data types.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
Photo by Volkan Olmez on Unsplash
OS and DB used:
- Xubuntu Linux 16.04.3 LTS (Xenial Xerus)
- MySQL 5.7.21
Be sure a visit both blog posts in the series so far:
- Aggregate Functions Part 1: COUNT() – With examples in MySQL
- Aggregate Functions Part 2: SUM() and AVG() in MySQL with examples.
Here is the description of the mock HR employees table we have been targeting for our queries:
mysql> DESC employees;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| EMPLOYEE_ID | decimal(6,0) | NO | PRI | 0 | |
| FIRST_NAME | varchar(20) | YES | | NULL | |
| LAST_NAME | varchar(25) | NO | MUL | NULL | |
| EMAIL | varchar(25) | NO | UNI | NULL | |
| PHONE_NUMBER | varchar(20) | YES | | NULL | |
| HIRE_DATE | date | NO | | NULL | |
| JOB_ID | varchar(10) | NO | MUL | NULL | |
| SALARY | decimal(8,2) | YES | | NULL | |
| COMMISSION_PCT | decimal(2,2) | YES | | NULL | |
| MANAGER_ID | decimal(6,0) | YES | MUL | NULL | |
| DEPARTMENT_ID | decimal(4,0) | YES | MUL | NULL | |
+----------------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| EMPLOYEE_ID | decimal(6,0) | NO | PRI | 0 | |
| FIRST_NAME | varchar(20) | YES | | NULL | |
| LAST_NAME | varchar(25) | NO | MUL | NULL | |
| EMAIL | varchar(25) | NO | UNI | NULL | |
| PHONE_NUMBER | varchar(20) | YES | | NULL | |
| HIRE_DATE | date | NO | | NULL | |
| JOB_ID | varchar(10) | NO | MUL | NULL | |
| SALARY | decimal(8,2) | YES | | NULL | |
| COMMISSION_PCT | decimal(2,2) | YES | | NULL | |
| MANAGER_ID | decimal(6,0) | YES | MUL | NULL | |
| DEPARTMENT_ID | decimal(4,0) | YES | MUL | NULL | |
+----------------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
MIN and MAX
MIN() and MAX() return the smallest or largest value out of a range of values, respectively.
But, that holds different implications dependent upon the data type operated on.
MIN() and MAX() can both accept either numeric, string, or date data type arguments.
Numeric Values
Let’s see how both functions operate on numeric values.
Suppose we have this range of 5 SALARY column values for the 'FI_ACCOUNT' role:
mysql> SELECT EMPLOYEE_ID, SALARY, JOB_ID
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-------------+---------+------------+
| EMPLOYEE_ID | SALARY | JOB_ID |
+-------------+---------+------------+
| 109 | 9000.00 | FI_ACCOUNT |
| 110 | 8200.00 | FI_ACCOUNT |
| 111 | 7700.00 | FI_ACCOUNT |
| 112 | 7800.00 | FI_ACCOUNT |
| 113 | 6900.00 | FI_ACCOUNT |
+-------------+---------+------------+
5 rows in set (0.00 sec)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-------------+---------+------------+
| EMPLOYEE_ID | SALARY | JOB_ID |
+-------------+---------+------------+
| 109 | 9000.00 | FI_ACCOUNT |
| 110 | 8200.00 | FI_ACCOUNT |
| 111 | 7700.00 | FI_ACCOUNT |
| 112 | 7800.00 | FI_ACCOUNT |
| 113 | 6900.00 | FI_ACCOUNT |
+-------------+---------+------------+
5 rows in set (0.00 sec)
I’ll apply MIN() and MAX() to this host of SALARY column values:
mysql> SELECT MIN(SALARY)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-------------+
| MIN(SALARY) |
+-------------+
| 6900.00 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT MAX(SALARY)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-------------+
| MAX(SALARY) |
+-------------+
| 9000.00 |
+-------------+
1 row in set (0.00 sec)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-------------+
| MIN(SALARY) |
+-------------+
| 6900.00 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT MAX(SALARY)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-------------+
| MAX(SALARY) |
+-------------+
| 9000.00 |
+-------------+
1 row in set (0.00 sec)
* Note: I removed the EMPLOYEE_ID and JOB_ID from the SELECT clause to prohibit needing a GROUP BY clause for demonstration purposes without error.
In these example queries, MIN() returned 6900.00 -the smallest value out of the list- and MAX() the largest, 9000.00.
As you can see, numeric value operations with MIN() and MAX() are relatively straight-forward.
Date Values
Date values are handled a little differently than numbers. The most current, newest date is considered the MAX() value, while MIN() returns the oldest date.
Some sample queries will help put this into perspective:
mysql> SELECT MAX(HIRE_DATE)
-> FROM employees;
+----------------+
| MAX(HIRE_DATE) |
+----------------+
| 1987-10-01 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT MIN(HIRE_DATE)
-> FROM employees;
+----------------+
| MIN(HIRE_DATE) |
+----------------+
| 1987-06-17 |
+----------------+
1 row in set (0.00 sec)
-> FROM employees;
+----------------+
| MAX(HIRE_DATE) |
+----------------+
| 1987-10-01 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT MIN(HIRE_DATE)
-> FROM employees;
+----------------+
| MIN(HIRE_DATE) |
+----------------+
| 1987-06-17 |
+----------------+
1 row in set (0.00 sec)
We’ll view a query with both functions side-by-side for a nice visual of the difference:
mysql> SELECT MAX(HIRE_DATE), MIN(HIRE_DATE)
-> FROM employees;
+----------------+----------------+
| MAX(HIRE_DATE) | MIN(HIRE_DATE) |
+----------------+----------------+
| 1987-10-01 | 1987-06-17 |
+----------------+----------------+
1 row in set (0.01 sec)
-> FROM employees;
+----------------+----------------+
| MAX(HIRE_DATE) | MIN(HIRE_DATE) |
+----------------+----------------+
| 1987-10-01 | 1987-06-17 |
+----------------+----------------+
1 row in set (0.01 sec)
As shown above, 1987-10-01 is a more recent date than 1987-06-17 in respect to both dates returned.
String Values
MIN() and MAX() can operate on string values as well. Let’s look at the FIRST_NAME column and test out both functions on that range of values for the 'FI_ACCOUNT' role:
mysql> SELECT FIRST_NAME
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-------------+
| FIRST_NAME |
+-------------+
| Daniel |
| John |
| Ismael |
| Jose Manuel |
| Luis |
+-------------+
5 rows in set (0.00 sec)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-------------+
| FIRST_NAME |
+-------------+
| Daniel |
| John |
| Ismael |
| Jose Manuel |
| Luis |
+-------------+
5 rows in set (0.00 sec)
mysql> SELECT MIN(FIRST_NAME)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-----------------+
| MIN(FIRST_NAME) |
+-----------------+
| Daniel |
+-----------------+
1 row in set (0.00 sec)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-----------------+
| MIN(FIRST_NAME) |
+-----------------+
| Daniel |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT MAX(FIRST_NAME)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-----------------+
| MAX(FIRST_NAME) |
+-----------------+
| Luis |
+-----------------+
1 row in set (0.01 sec)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-----------------+
| MAX(FIRST_NAME) |
+-----------------+
| Luis |
+-----------------+
1 row in set (0.01 sec)
From the range of names returned by the first query, the following queries show that: MIN() and MAX() consider alphabetical ordering in this context.
* FYI:
MIN() and MAX()‘s functionality can potentially hinge on the character set and collation used within MySQL.
For instance, to know the system-wide setting for my local install, we can see with this SHOW VARIABLES statement:
mysql> SHOW VARIABLES LIKE 'character_set_system';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_system | utf8 |
+----------------------+-------+
1 row in set (0.01 sec)
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_system | utf8 |
+----------------------+-------+
1 row in set (0.01 sec)
The character set at the ‘system-level’ is utf8.
And for the mock employees table I am using throughout these blog posts,
mysql> SHOW CREATE TABLE employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
..........
..........)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
..........
..........)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
latin1 is enabled as the character set.
Admittedly, this is a ‘gray area’ for me in MySQL.
Yet, I continue to study the official documentation from Chapter 10 Character Sets, Collations, Unicode to better understand my knowledge base ‘gap’ in regards to collation.
I encourage you all to consult that resource as well, for any questions you may have regarding character sets and collation.
Conclusion
Wrapping up this series, I hope you have a better understanding of those aggregate functions covered. I’ve not covered all aggregate functions available in MySQL, mainly just those you’ll likely encounter.
They all provide a multitude of uses. I would love knowing how you utilized them in various contexts, so feel free to leave a comment below.
Visit the official MySQL 5.7 On-line Manual for more information.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.