MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL’ Category

MySQL DropIndexIfExists

without comments

In reply to a question about how to conditionally drop an index on a table in MySQL. It appears the syntax doesn’t exist. However, maybe it does and I missed it. If I did miss it, I’m sure somebody will let me know. However, I simply have a dropIndexIfExists stored procedure for this type of database maintenance.

Below is my dropIndexIfExists stored procedure:

-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropIndexIfExists;
 
-- Change the default semicolon delimiter to write a PSM
-- (Persistent Stored Module) or stored procedure.
DELIMITER $$
 
-- Create the procedure.
CREATE PROCEDURE dropIndexIfExists
( pv_table_name  VARCHAR(64)
, pv_index_name  VARCHAR(64))
BEGIN
 
  /* Declare a local variable for the SQL statement. */
  DECLARE stmt VARCHAR(1024);
 
  /* Set a session variable with two parameter markers. */
  SET @SQL := CONCAT('ALTER TABLE ',pv_table_name,'DROP INDEX ',pv_index_name);
 
  /* Check if the constraint exists. */
  IF EXISTS (SELECT NULL
             FROM   information_schema.statistics s
             WHERE  s.index_schema = database()
             AND    s.table_name = pv_table_name
             AND    s.index_name = pv_index_name)
  THEN
 
    /* Dynamically allocated and run statement. */
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
 
END;
$$
 
-- Reset the default semicolon delimiter.
DELIMITER ;

You call the procedure like:

CALL dropIndexIfExists('payment','idx_payment01');

As always, I hope this helps those looking for a solution.

Written by maclochlainn

December 1st, 2021 at 12:09 am

Posted in MySQL,MySQL 8,Persistent Stored Modules,sql

Tagged with , ,

MySQL 8+ Catalog

without comments

I was working through some tutorials for my students and noticed that there was a change in how a WHERE clause must be written against the information_schema.table_constraints table. It might have been made in an earlier release, I actually hadn’t checked it since 2014 when I wrote this early post on capturing MySQL Foreign Keys.

You could use the following WHERE case insensitive clause:

WHERE    tc.constraint_type = 'foreign key'

Now, you must use a case sensitive WHERE clause:

WHERE    tc.constraint_type = 'FOREIGN KEY'

I’d love to know why but I can’t seem to find a note on the change. As always, I hope this helps those looking for an answer.

Written by maclochlainn

November 30th, 2021 at 11:06 pm

Posted in MySQL,MySQL 8,sql

MySQL WITH Clause

without comments

When I went over my example of using the WITH clause to solve how to use a series of literal values in data sets, some students got it right away and some didn’t. The original post showed how to solve a problem where one value in the data set is returned in the SELECT-list and two values are used as the minimum and maximum values with a BETWEEN operator. It used three approaches with literal values:

  • A list of Python dictionaries that require you to filter the return set from the database through a range loop and if statement that mimics a SQL BETWEEN operator.
  • A WITH clause that accepts the literals as bind variables to filter the query results inside the query.
  • A table design that holds the literals values that an analyst might use for reporting.

It was the last example that required elaboration. I explained you might build a web form that uses a table, and the table could allow a data analyst to enter parameter sets. That way the analyst could submit a flag value to use one or another set of values. I threw out the idea on the whiteboard of introducing a report column to the prior post’s level table. The student went off to try it.

Two problems occurred. The first was in the design of the new table and the second was how to properly use the MySQL Python driver.

Below is a formal table design that supports this extension of the first blog post as a list of parameter values. It uses a report column as a super key to return a set of possible values. One value will show in the SELECT-list and the other two values deploy as the minimum and maximum values in a BETWEEN operator. It is seeded with two sets of values. One of the report possibilities is Summary level with three possibilities and the other is the Detail level with five possibilities.

-- Conditionally drop the levels table.
DROP TABLE IF EXISTS levels;
 
-- Create the levels list.
CREATE TABLE levels
( level      VARCHAR(16)
, report     ENUM('Summary','Detail')
, min_roles  INT
, max_roles  INT );
 
-- Insert values into the list table.
INSERT INTO levels
( level, report, min_roles, max_roles )
VALUES
 ('Hollywood Star','Summary', 30, 99999)
,('Prolific Actor','Summary', 20, 29)
,('Newcommer','Summary', 1, 19)
,('Hollywood Star','Detail', 30, 99999)
,('Prolific Actor','Detail', 20, 29)
,('Regular Actor','Detail', 10, 19)
,('Actor','Detail', 5, 9)
,('Newcommer','Detail', 1, 4);

The foregoing table design uses an ENUM type because reporting parameter sets are typically fewer than 64 possibilities. If you use the table to support multiple reports, you should add a second super key column like report_type. The report_type column key would let you use the table to support a series of different report parameter lists.

While the student used a %s inside the query, they created a runtime error when trying to pass the single bind variable into the query. The student misunderstood how to convert a report column input parameter variable into a tuple, which shows up when the student calls the Python MySQL Driver, like this:

59

cursor.execute(query, (report))

The student’s code generated the following error stack:

Traceback (most recent call last):
  File "./python-with-clause.py", line 59, in <module>
    cursor.execute(query,(report))
  File "/usr/lib/python3.7/site-packages/mysql/connector/cursor_cext.py", line 248, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/usr/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 632, in prepare_for_mysql
    raise ValueError("Could not process parameters")
ValueError: Could not process parameters

The ValueError should indicate to the developer that they’ve used a wrong data type in the call to the method:

cursor.execute(<class 'str'>,<class 'tuple'>)

This clearly was a misunderstanding of how to cast a single string to a tuple. A quick explanation of how Python casts a single string into a tuple can best be illustrated inside an interactive Python shell, like:

>>> # Define a variable.
>>> x = 'Detail'
>>> # An incorrect attempt to make a string a tuple.
>>> y = (x)
>>> # Check type of y after assignment.
>>> print(type(y))
<class 'str'>
>>> # A correct attempt to make a string a tuple.
>>> y = tuple(x)
>>> # Check type of y after assignment.
>>> print(type(y))
<class 'tuple'>
>>> # An alternative to make a string a tuple.
>>> z = (x,)
>>> # Check type of z after assignment.
>>> print(type(z))
<class 'tuple'>

So, the fix was quite simple to line 59:

59

cursor.execute(query, (report,))

The student started with a copy of a Python program that I provided. I fixed the argument handling and added some comments. The line 59 reference above maps to this code example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

# Import the library.
import sys
import mysql.connector
from mysql.connector import errorcode
 
# Capture argument list.
fullCmdArguments = sys.argv
 
# Assign argument list to variable.
argumentList = fullCmdArguments[1:]
 
# Define a standard report variable.
report = "Summary"
 
#  Check and process argument list.
# ============================================================
#  If there are less than two arguments provide default values.
#  Else enumerate and convert strings to dates.
# ============================================================
if (len(argumentList) == 1):
  # Set a default start date.
  if (isinstance(report,str)):
    report = argumentList[0]
 
#  Attempt the query.
# ============================================================
#  Use a try-catch block to manage the connection.
# ============================================================
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='sakila')
  # Create cursor.
  cursor = cnx.cursor()
 
  # Set the query statement.
  query = ("WITH actors AS "
           "(SELECT   a.actor_id "
           " ,        a.first_name "
           " ,        a.last_name "
           " ,        COUNT(fa.actor_id) AS num_roles "
           " FROM     actor a INNER JOIN film_actor fa "
           " ON       a.actor_id = fa.actor_id "
           " GROUP BY a.actor_id "
           " ,        a.first_name "
           " ,        a.last_name ) "
           " SELECT   a.first_name "
           " ,        a.last_name "
           " ,        l.level "
           " ,        a.num_roles "
           " FROM     actors a CROSS JOIN levels l "
           " WHERE    a.num_roles BETWEEN l.min_roles AND l.max_roles "
           " AND      l.report = %s "
           " ORDER BY a.last_name "
           " ,        a.first_name")
 
  # Execute cursor.
  cursor.execute(query,(report,))
 
  # Display the rows returned by the query.
  for (first_name, last_name, level, num_roles) in cursor:
    print('{0} {1} is a {2} with {3} films.'.format( first_name.title()
                                                   , last_name.title()
                                                   , level.title()
                                                   , num_roles))
 
  # Close cursor.
  cursor.close()
 
# ------------------------------------------------------------
# Handle exception and close connection.
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message
 
# Close the connection when the try block completes.
else:
  cnx.close()

A Linux shell program like the following (provided the name of the shell script and Python program are the same) can run the Python program with or without a parameter. It works without a parameter because it sets a default value for the report variable.

# Switch the file extension and run the python program.
file=${0/%sh/py}
python3 ${file} "${@}"

You call the shell script like this:

./python-with-clause.sh Detail

As always, I hope this helps those looking for a solution.

Written by maclochlainn

November 14th, 2021 at 11:01 pm

Posted in Linux,MySQL,MySQL 8,Python,Python 3.x,sql,Unix

Tagged with ,

MySQL with CTEs

without comments

As an example for my class on the usefulness of Common Table Expressions (CTEs), I created three examples with Python. They extend an exercise in Chapter 9 on subqueries from Learning SQL by Alan Beaulieu. All of the examples work with the sakila sample database.

These bullets describe the examples:

  1. Uses local variables and a range for loop and if statement that uses the variables to evaluate and add an element to the derived table (or query result set) from MySQL.
  2. Uses a CTE with substitution variables from the Python program, which eliminates the need to evaluate and add an element to the query result set because the query does that.
  3. Uses a table to hold the variables necessary to evaluate and add the element to the query result set.

    4. This is the first Python program:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

    # Import the library.
import sys
import mysql.connector
from mysql.connector import errorcode
 
# Declare a list of tuples.
dict = [{'level':'Hollywood Star','min_roles':30,'max_roles':99999}
       ,{'level':'Prolific Actor','min_roles':20,'max_roles':29}
       ,{'level':'Newcomer','min_roles':1,'max_roles':19}]
 
#  Attempt the query.
# ============================================================
#  Use a try-catch block to manage the connection.
# ============================================================
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='sakila')
  # Create cursor.
  cursor = cnx.cursor()
 
  # Set the query statement.
  query = ("SELECT   a.actor_id "
           ",        a.first_name       "
           ",        a.last_name "
           ",        COUNT(fa.actor_id) AS films "
           "FROM     actor a INNER JOIN film_actor fa "
           "ON       a.actor_id = fa.actor_id "
           "GROUP BY a.actor_id "
           ",        a.first_name "
           ",        a.last_name "
           "ORDER BY a.last_name "
           ",        a.first_name")
 
  # Execute cursor.
  cursor.execute(query)
 
  # Display the rows returned by the query.
  for (actor_id, first_name, last_name, films) in cursor:
    for i in range(len(dict)):
      if films >= dict[i]["min_roles"] and films <= dict[i]["max_roles"]:
        print('{0} {1} is a {2} with {3} films.'.format( first_name.title()
                                                       , last_name.title()
                                                       , dict[i]["level"]
                                                       , films))
 
  # Close cursor.
  cursor.close()
 
# ------------------------------------------------------------
# Handle exception and close connection.
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message
 
# Close the connection when the try block completes.
else:
  cnx.close()

    The Python dictionary on lines 7 thru 9 and range for loop and if statement on lines 41 and 42 can be eliminated by putting the literal values in a Common Table Expression (CTE). That’s because a CROSS JOIN matches all rows in the CTE against the base table before filtering them.

    The match of all rows in the CTE against the base table effectively replaces the range for loop in the original code. The WHERE clause replaces the if statement in the original code.

    Another optimization for readability of the final query puts the grouped query into a CTE as well. That way the final query simply demonstrates the filtering process.

    This is the second Python program, and it converts the Python dictionary to a list of lists and assigns the lists to param tuple:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

    # Import the library.
import sys
import mysql.connector
from mysql.connector import errorcode
 
# Declare a list of lists.
list = [['Hollywood Star',30,99999]
       ,['Prolific Actor',20,29]
       ,['Newcomer',1,19]]
 
# Declare a tuple of the set of lists.
param = (list[0] + list[1] + list[2])
 
#  Attempt the query.
# ============================================================
#  Use a try-catch block to manage the connection.
# ============================================================
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='sakila')
  # Create cursor.
  cursor = cnx.cursor()
 
  # Set the query statement.
  query = ("WITH actors AS "
           "(SELECT   a.actor_id "
           " ,        a.first_name "
           " ,        a.last_name "
           " ,        COUNT(fa.actor_id) AS num_roles "
           " FROM     actor a INNER JOIN film_actor fa "
           " ON       a.actor_id = fa.actor_id "
           " GROUP BY a.actor_id "
           " ,        a.first_name "
           " ,        a.last_name ) "
           " , levels AS "
           "(SELECT  %s AS level "
           " ,       %s AS min_roles "
           " ,       %s AS max_roles "
           " UNION ALL "
           " SELECT  %s AS level "
           " ,       %s AS min_roles "
           " ,       %s AS max_roles "
           " UNION ALL "
           " SELECT  %s AS level "
           " ,       %s AS min_roles "
           " ,       %s AS max_roles) "
           " SELECT a.first_name "
           " ,      a.last_name "
           " ,      l.level "
           " ,      a.num_roles "
           " FROM   actors a CROSS JOIN levels l "
           " WHERE  a.num_roles BETWEEN l.min_roles AND l.max_roles "
           " ORDER BY a.last_name "
           " ,        a.first_name")
 
  # Execute cursor.
  cursor.execute(query, param)
 
  # Display the rows returned by the query.
  for (first_name, last_name, level, num_roles) in cursor:
    print('{0} {1} is a {2} with {3} films.'.format( first_name.title()
                                                   , last_name.title()
                                                   , level.title()
                                                   , num_roles))
 
  # Close cursor.
  cursor.close()
 
# ------------------------------------------------------------
# Handle exception and close connection.
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message
 
# Close the connection when the try block completes.
else:
  cnx.close()

    This is the third Python program requires some SQL setup. You should run this script inside the sakila database first. It basically takes the variables out of the code and stores them in a table. This is more likely what you would do to ensure maintainability of ever changing range values like these if you built a solution like this in a real application. It leaves the aggregation process inside a CTE and simplifies the final query.

    -- Conditionally drop the levels table.
DROP TABLE IF EXISTS levels;
 
-- Create the levels list.
CREATE TABLE levels
( level      VARCHAR(16)
, min_roles  INT
, max_roles  INT );
 
-- Insert values into the list table.
INSERT INTO levels
( level, min_roles, max_roles )
VALUES
 ('Hollywood Star', 30, 99999)
,('Prolific Actor', 20, 29)
,('Newcommer',1,19);

    After seeding the data in the levels table, you can test the query natively in MySQL, like this:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

    -- Query the data.
WITH actors AS
 (SELECT   a.actor_id
  ,        a.first_name
  ,        a.last_name
  ,        COUNT(*) AS num_roles
  FROM     actor a INNER JOIN film_actor fa
  ON       a.actor_id = fa.actor_id
  GROUP BY actor_id)
SELECT   a.first_name
,        a.last_name
,        l.level
,        a.num_roles
FROM     actors a CROSS JOIN levels l
WHERE    a.num_roles BETWEEN l.min_roles AND l.max_roles
ORDER BY a.last_name
,        a.first_name;

    There’s also a syntax that makes this type of query appear to be an INNER JOIN when it’s actually a filtered CROSS JOIN. If you adopt that syntax, you would rewrite lines 14 and 15:

    14
15

    FROM   actors a INNER JOIN levels l
WHERE  a.num_roles BETWEEN l.min_roles AND l.max_roles;

    Then, you can run this version without the second CTE element:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

    # Import the library.
import sys
import mysql.connector
from mysql.connector import errorcode
 
#  Attempt the query.
# ============================================================
#  Use a try-catch block to manage the connection.
# ============================================================
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='sakila')
  # Create cursor.
  cursor = cnx.cursor()
 
  # Set the query statement.
  query = ("WITH actors AS "
           "(SELECT   a.actor_id "
           " ,        a.first_name "
           " ,        a.last_name "
           " ,        COUNT(fa.actor_id) AS num_roles "
           " FROM     actor a INNER JOIN film_actor fa "
           " ON       a.actor_id = fa.actor_id "
           " GROUP BY a.actor_id "
           " ,        a.first_name "
           " ,        a.last_name ) "
           " SELECT   a.first_name "
           " ,        a.last_name "
           " ,        l.level "
           " ,        a.num_roles "
           " FROM     actors a CROSS JOIN levels l "
           " WHERE    a.num_roles BETWEEN l.min_roles AND l.max_roles "
           " ORDER BY a.last_name "
           " ,        a.first_name")
 
  # Execute cursor.
  cursor.execute(query)
 
  # Display the rows returned by the query.
  for (first_name, last_name, level, num_roles) in cursor:
    print('{0} {1} is a {2} with {3} films.'.format( first_name.title()
                                                   , last_name.title()
                                                   , level.title()
                                                   , num_roles))
 
  # Close cursor.
  cursor.close()
 
# ------------------------------------------------------------
# Handle exception and close connection.
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message
 
# Close the connection when the try block completes.
else:
  cnx.close()

    As always, I hope this helps those trying to understand how CTEs can solve problems that would otherwise be coded in external imperative languages like Python.

Written by maclochlainn

November 3rd, 2021 at 10:01 am

Posted in Fedora,Linux,MySQL,MySQL 8,Python,Python 3.x,sql

Tagged with , ,

MySQL and JavaScript

without comments

Sometimes students truly amaze me with their curiosity. The question was simple from their perspective while we were discussing MySQL’s builtin string functions. How would you do something like this JavaScript logic without using literals or session variables?

// Declare a string and substring.
var myStr = 'Get me from the string.'
var mySubstr = 'me'
 
// Assign the substring to variable by rescuing it from the larger string.
var rescued = myStr.substring(myStr.indexOf(mySubstr),myStr.indexOf(mySubstr) + mySubstr.length)
 
// Print the result.
print(rescued)

tested with MongoDB, like

mongo --nodb --norc < parsing.js

returning:

MongoDB shell version v4.0.20
me
bye

They thought the question would show SQL’s limits as a problem solving and programming language because they didn’t see how MySQL could assign a variable for evaluation in the builtin functions.

They were surprised to see how I showed them that they could do it. Since they disallowed session variables, I built a sample table and inserted the string value in a text column before writing a query with a Common Table Expression (CTE).

The MySQL steps are:

-- Stable testing scenario with table values requires a test table.
DROP TABLE IF EXISTS sample;
CREATE TABLE sample
( text  VARCHAR(100) );
 
-- Insert the literal string into the testing table.
INSERT INTO sample
( text )
VALUES
('Get me from the string.');
 
-- Test using a WITH clause to place a variable in context for use
-- in the query, relying on the fact that a Cartesian set of one
-- column and row becomes a new column in all rows of the other
-- table's set.
WITH struct AS
(SELECT 'me' AS result)
 SELECT SUBSTR(text,INSTR(text,struct.result),LENGTH(struct.result)) AS rescued
 FROM   sample CROSS JOIN struct;

It returns the following:

+---------+
| rescued |
+---------+
| me      |
+---------+
1 row in set (0.00 sec)

Wow, SQL works like a programming language was the response of the class. It’s like anything else in technology, new stuff isn’t as cool as old stuff until you learn how to use it.

Written by maclochlainn

October 24th, 2021 at 10:48 am

Posted in JavaScript,Linux,MySQL,MySQL 8,MySQL Client,MySQL Shell,sql

Tagged with , ,

MySQL SQL Filters

without comments

An interesting outcome of teaching SQL is discovering what skills new users require. One that I continuously rediscover is how to build a test case for various elements of SQL. This is a small article on querying with filters in the WHERE clause.

There are several of the exercises in Alan Beaulieu’s Learning SQL, 3rd Edition that would benefit from example setup. For example, Chapter 4 provides a snapshot of the payment table but doesn’t provide any instructions.

You can create an exercise_4_2 table with the following SQL statement if you plan to change the data:

CREATE TABLE exercise_4_2 AS
SELECT payment_id
,      customer_id
,      amount
,      payment_date
FROM   payment
WHERE  payment_id BETWEEN 101 AND 120;

Alternatively, you can create an exercise_4_2 view with the following SQL statement if you plan to only query the data:

CREATE VIEW exercise_4_2 AS
SELECT payment_id
,      customer_id
,      amount
,      payment_date
FROM   payment
WHERE  payment_id BETWEEN 101 AND 120;

After creating the new exercise_4_2 table or view from the payment table it will hold a subset of data. You can query all the rows from the new exercise_4_2 table or view with this statement:

SELECT *
FROM   exercise_4_2;

It returns the following data set:

+------------+-------------+--------+---------------------+
| payment_id | customer_id | amount | payment_date        |
+------------+-------------+--------+---------------------+
|        101 |           4 |   8.99 | 2005-08-18 05:14:44 |
|        102 |           4 |   1.99 | 2005-08-19 02:19:13 |
|        103 |           4 |   2.99 | 2005-08-20 09:32:04 |
|        104 |           4 |   6.99 | 2005-08-20 12:55:40 |
|        105 |           4 |   4.99 | 2005-08-21 04:53:37 |
|        106 |           4 |   2.99 | 2005-08-22 13:58:23 |
|        107 |           4 |   1.99 | 2005-08-23 07:43:00 |
|        108 |           5 |   0.99 | 2005-05-29 07:25:16 |
|        109 |           5 |   6.99 | 2005-05-31 11:15:43 |
|        110 |           5 |   1.99 | 2005-05-31 19:46:38 |
|        111 |           5 |   3.99 | 2005-06-15 22:03:14 |
|        112 |           5 |   2.99 | 2005-06-16 08:01:02 |
|        113 |           5 |   4.99 | 2005-06-17 15:56:53 |
|        114 |           5 |   2.99 | 2005-06-19 04:20:13 |
|        115 |           5 |   4.99 | 2005-06-20 18:38:22 |
|        116 |           5 |   4.99 | 2005-07-06 09:11:58 |
|        117 |           5 |   2.99 | 2005-07-08 20:04:43 |
|        118 |           5 |   4.99 | 2005-07-09 01:57:57 |
|        119 |           5 |   5.99 | 2005-07-09 07:13:52 |
|        120 |           5 |   1.99 | 2005-07-09 08:51:42 |
+------------+-------------+--------+---------------------+
20 rows in set (0.26 sec)

With the exercise_4_2 table, you can test the exercises 4-1 and 4-2. Here are the two problems:

  • The first exercise checks for rows where the customer_id is not equal to 5 and whether the amount is greater than 8 or payment_date is equal to ‘2005-08-23’. You can structure that question as the following query: 

    mysql> SELECT   *
    -> FROM     exercise_4_2
    -> WHERE    customer_id <> 5
    -> AND     (amount > 8 OR DATE(payment_date) = '2005-08-23');

    It would return the following two rows from the exercise_4_2 table:

    +------------+-------------+--------+---------------------+
| payment_id | customer_id | amount | payment_date        |
+------------+-------------+--------+---------------------+
|        101 |           4 |   8.99 | 2005-08-18 05:14:44 |
|        107 |           4 |   1.99 | 2005-08-23 07:43:00 |
+------------+-------------+--------+---------------------+
2 rows in set (0.14 sec)
  • The second exercise checks for rows where the customer_id is equal to 5 and whether the amount is not greater than 6 or payment_date is not equal to ‘2005-06-19’. You can structure that question as the following query: 

    mysql> SELECT   *
    -> FROM     exercise_4_2
    -> WHERE    customer_id = 5
    -> AND NOT (amount > 6 OR DATE(payment_date) = '2005-06-19');

    It would return the following eleven rows from the exercise_4_2 table:

    +------------+-------------+--------+---------------------+
| payment_id | customer_id | amount | payment_date        |
+------------+-------------+--------+---------------------+
|        108 |           5 |   0.99 | 2005-05-29 07:25:16 |
|        110 |           5 |   1.99 | 2005-05-31 19:46:38 |
|        111 |           5 |   3.99 | 2005-06-15 22:03:14 |
|        112 |           5 |   2.99 | 2005-06-16 08:01:02 |
|        113 |           5 |   4.99 | 2005-06-17 15:56:53 |
|        115 |           5 |   4.99 | 2005-06-20 18:38:22 |
|        116 |           5 |   4.99 | 2005-07-06 09:11:58 |
|        117 |           5 |   2.99 | 2005-07-08 20:04:43 |
|        118 |           5 |   4.99 | 2005-07-09 01:57:57 |
|        119 |           5 |   5.99 | 2005-07-09 07:13:52 |
|        120 |           5 |   1.99 | 2005-07-09 08:51:42 |
+------------+-------------+--------+---------------------+
11 rows in set (0.00 sec)
  • The third exercise checks for payment_id and amount values where the amount is either 1.98, 7.98, or 9.98. You can structure that question as the following query: 

    mysql> SELECT payment_id
    -> ,      amount
    -> FROM   payment
    -> WHERE  amount IN (1.98,7.98,9.98);

    It would return the following seven rows from the payment table:

    +------------+--------+
| payment_id | amount |
+------------+--------+
|       1482 |   7.98 |
|       1670 |   9.98 |
|       2901 |   1.98 |
|       4234 |   7.98 |
|       4449 |   7.98 |
|       7243 |   7.98 |
|       9585 |   7.98 |
+------------+--------+
7 rows in set (0.00 sec)
  • The fourth exercise checks for the first_name and last_name of customers where the last_name contains an ‘A’ in the second position and a ‘W’ after the ‘A’ character. You can structure that question as the following query: 

    mysql> SELECT first_name
    -> ,      last_name
    -> FROM   customer
    -> WHERE  last_name LIKE '_A%W%';

    The trick to the WHERE clause is that the ‘%’ looks for zero to many characters in between two strings.

    It would return the following nine rows from the customer table:

    +------------+------------+
| first_name | last_name  |
+------------+------------+
| JILL       | HAWKINS    |
| ERICA      | MATTHEWS   |
| LAURIE     | LAWRENCE   |
| JEANNE     | LAWSON     |
| KAY        | CALDWELL   |
| JOHN       | FARNSWORTH |
| SAMUEL     | MARLOW     |
| LAWRENCE   | LAWTON     |
| LEE        | HAWKS      |
+------------+------------+
9 rows in set (0.10 sec)

As always, I hope this helps those looking for a solution.

Written by maclochlainn

October 4th, 2021 at 12:39 am

Posted in MySQL,MySQL 8,sql

Tagged with ,

Title Case Anyone?

without comments

Sometimes life is too surreal. Like when somebody says, “How do you get title case in an Oracle database?” That’s when you know three things about the individual, while suppressing laughter. They’re not very experienced with SQL, likely lazy, and don’t read the documentation.

I had a little fun with somebody today by taking them down a small rat-hole. “Oh, gosh … ” I said, “… let’s write a function for that.” Here’s the joke function, like:

CREATE OR REPLACE
FUNCTION title_case
( string VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  /* Change upper case to title case. */
  RETURN UPPER(SUBSTR(string,1,1)) || LOWER(SUBSTR(string,2,LENGTH(string)));
END title_case;
/

Then, we tested it with a query from the pseudo dual table:

SELECT title_case('incredible') AS "Proper Name" FROM dual;

It returned:

Proper Name
----------
Incredible

Then, I said “Oh, that’s not his proper name in the Pixar World.” It should be: Mr. Incredible. Let’s try that:

SELECT title_case('mr. incredible') AS "Proper Name" FROM dual;

It returned:

Proper Name
--------------
Mr. incredible

Then, I said: “That’s not what we want at all. Should we rewrite our function or simply use the INITCAP built-in function?” Then, I wrote:

SELECT INITCAP('mr. incredible') AS "Proper Name" FROM dual;

It returned:

Proper Name
--------------
Mr. Incredible

Well, needless to say my acquaintance got it immediately and said “I should have Googled it or read the documentation.” I concurred with his observation.

Just sharing a cute day’s event that made me laugh and cry at the same time because there are too many who say SQL isn’t worth learning.

Written by maclochlainn

June 30th, 2021 at 11:49 am

Posted in Linux,MySQL,MySQL 8,Oracle,Oracle 18c,sql,Unix,Windows 10

Tagged with ,

MySQL PHP Transaction

without comments

My students liked the MySQL Transaction post but wanted one that showed how an external web application would interact with MySQL in the scope of a transaction. So, I put a little PHP function together that write across two related tables in the context of a transaction. It uses mysqli (MySQL Improved Extension) to connect PHP to the MySQL database.

The function is barebones and uses the oldest approach of hidden inputs to maintain context between rendered forms using an HTML POST method. The hidden inputs are preceded with “h_” and snake case is used for variable names.

The function only writes to two tables. It writes to the member table and when that completes successfully to the contact table. The function:

  • Submits credentials from a file and raises an error when they don’t work.
  • Initializes a SQL statement.
  • Disables auto commit.
  • Starts a transaction.
  • Defines a first SQL statement with placeholders.
  • Binds local variables to the first SQL statement’s placeholders.
  • Rolls back the transaction when the first statement fails and continues to the next SQL statement when first statement succeeds.
  • Defines a second SQL statement with placeholders.
  • Binds local variables to the second SQL statement’s placeholders.
  • Rolls back the transaction when the second statement fails and commits the work when the second statement succeeds.
  • Closes a SQL statement.

The code for the PHP function is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129

/*
||  Function Name: update_membership
|| ----------------------------------------------------------------------
||  No return, treated as a procedure, or method returning a void.
|| ----------------------------------------------------------------------
*/
function update_membership(
    $h_account_number
  , $h_member_type
  , $h_credit_card_number
  , $h_credit_card_type
  , $account_number
  , $member_type
  , $credit_card_number
  , $credit_card_type
  , $h_first_name
  , $h_middle_name
  , $h_last_name
  , $h_contact_type
  , $first_name
  , $middle_name
  , $last_name
  , $contact_type) {
 
  // Include the credentials file if omitted.
  include_once("MySQLCredentials.inc");
 
  // Assign credentials to connection.
  $mysqli = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE);
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
    // Initial statement.
    $stmt = $mysqli->stmt_init();
 
    // Disable auto commit when you want two plus statements run.
    $mysqli->autocommit(FALSE);
 
    // Set the transaction guarantee.
    $mysqli->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
 
    // Declare a static query.
    $sql = "UPDATE   member\n"
         . "SET      member_type = ?\n"
         . ",        credit_card_number = ?\n"
         . ",        credit_card_type = ?\n"
         . "WHERE    account_number = ?\n"
         . "AND      member_type = ?\n"
         . "AND      credit_card_number = ?\n"
         . "AND      credit_card_type = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param(
          "sssssss"
         , $member_type
         , $credit_card_number
         , $credit_card_type
         , $account_number
         , $h_member_type
         , $h_credit_card_number
         , $h_credit_card_type); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Rollback or undo the transaction.
      $mysqli->rollback();
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />\n";
      print "Failed to resolve query ...<br />\n";
    }
 
    // Declare a static query.
    $sql = "UPDATE   contact\n"
         . "SET      first_name = ?\n"
         . ",        middle_name = ?\n"
         . ",        last_name = ?\n"
         . ",        contact_type = ?\n"
         . "WHERE    first_name = ?\n"
         . "AND      middle_name = ?\n"
         . "AND      last_name = ?\n"
         . "AND      contact_type = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param(
          "ssssssss"
        , $first_name
        , $middle_name
        , $last_name
        , $contact_type
        , $h_first_name
        , $h_middle_name
        , $h_last_name
        , $h_contact_type); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Rollback or undo the transaction.
      $mysqli->rollback();
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }
    else {   
      /*  Manually commiting writes when you have disabled the
      ||  default auto commit setting, explained above.
      || ------------------------------------------------------------
      ||  You would add the following command to commit the 
      ||  transaction.
      ||  ------------------------------
      ||   $mysqli->commit();
      || ------------------------------------------------------------
      */
      $mysqli->commit();
 
      // Close the transaction.
      $mysqli->close();
    }
  }
}

Line 41 disables auto commit and line 44 starts the transaction. Each statement is managed with the subsequent statement nested inside a block of code that is only reachable when the prior statement succeeds. While this only uses the member and contact table, it could use any number of tables. The natural alternative is building an updatable view.

As always, I hope this helps anybody looking for a code complete example.

Written by maclochlainn

June 29th, 2021 at 5:23 pm

Posted in LAMP,Linux,MySQL,MySQL 8,mysqli,sql

Tagged with ,

SQL Handling Nulls

without comments

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 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);

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;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       1 | One        |       1 |        1 | One         |
|       3 | Three      |       3 |        2 | Three       |
|       5 | Five       |       5 |        4 | Five        |
+---------+------------+---------+----------+-------------+
3 rows in set (0.00 sec)

LEFT OUTER JOIN

The LEFT OUTER JOIN only returns those rows that match between a primary and foreign key column or set of columns and any rows in the table on the lefthand side of the join that fail to match with any row on the righthand side of the join. The non-matching rows are also known as the right complement of the join.

1
2
3
4
5
6
7

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

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       1 | One        |       1 |        1 | One         |
|       2 | Two        |    NULL |     NULL | NULL        |
|       3 | Three      |       3 |        2 | Three       |
|       5 | Five       |       5 |        4 | Five        |
+---------+------------+---------+----------+-------------+
4 rows in set (0.00 sec)

Add the following line 8 to the query and you get only those rows in the lefthand table that have no child-related rows in the righthand table. These rows are sometimes called childless parent rows. More or less, the use case for this type of query is to find order headers without order lines.

6
7
8

FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id
WHERE  r.left_id IS NULL;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       2 | Two        |    NULL |     NULL | NULL        |
+---------+------------+---------+----------+-------------+
1 row in set (0.00 sec)

RIGHT OUTER JOIN

1
2
3
4
5
6
7

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

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       1 | One        |       1 |        1 | One         |
|       3 | Three      |       3 |        2 | Three       |
|    NULL | NULL       |       4 |        3 | Four        |
|       5 | Five       |       5 |        4 | Five        |
+---------+------------+---------+----------+-------------+
4 rows in set (0.00 sec)

Add the following line 8 to the query and you get only those rows in the righthand table that have no parent-related rows in the lefthand table. These rows are sometimes called orphans because they have no parent row. More or less, the use case for this type of query is to find latent order lines after deleting the order header.

6
7
8

FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id
WHERE  l.left_id IS NULL;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|    NULL | NULL       |       4 |        3 | Four        |
+---------+------------+---------+----------+-------------+
1 row in set (0.00 sec)

FULL OUTER JOIN

The full outer join doesn’t exist in MySQL, so you combine a LEFT OUTER JOIN and RIGHT OUTER JOIN with the UNION operator. The UNION operator eliminates the duplicate row from the intersection of the joins.

Here’s the full query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

WITH cte AS
(SELECT l.left_id AS primary_left_id
,      l.leftstring
,      r.left_id  AS foreign_left_id
,      r.right_id
,      r.rightstring
FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id
UNION
SELECT l.left_id  AS primary_left_id
,      l.leftstring
,      r.left_id  AS foreign_left_id
,      r.right_id
,      r.rightstring
FROM   left_table l RIGHT JOIN right_table r
ON     l.left_id = r.left_id)
SELECT * FROM cte
ORDER BY
CASE
  WHEN 'One'   IN (leftstring, rightstring) THEN 1
  WHEN 'Two'   IN (leftstring, rightstring)  THEN 2
  WHEN 'Three' IN (leftstring, rightstring)  THEN 3
  WHEN 'Four'  IN (leftstring, rightstring)  THEN 4
  WHEN 'Five'  IN (leftstring, rightstring)  THEN 5
END;

It produces the following result set:

+-----------------+------------+-----------------+----------+-------------+
| primary_left_id | leftstring | foreign_left_id | right_id | rightstring |
+-----------------+------------+-----------------+----------+-------------+
|               1 | One        |               1 |        1 | One         |
|               2 | Two        |            NULL |     NULL | NULL        |
|               3 | Three      |               3 |        2 | Three       |
|            NULL | NULL       |               4 |        3 | Four        |
|               5 | Five       |               5 |        4 | Five        |
+-----------------+------------+-----------------+----------+-------------+
5 rows in set (0.00 sec)

Add the following lines 18 and 19 to the query and you get only those rows that are childless parent rows or orphaned child rows. More or less, the use case for this type of query is to find both order headers without order lines and order lines abandoned by deleted order headers.

17
18
19

SELECT * FROM cte
WHERE  primary_left_id IS NULL
OR     foreign_left_id IS NULL

It produces the following result set:

+-----------------+------------+-----------------+----------+-------------+
| primary_left_id | leftstring | foreign_left_id | right_id | rightstring |
+-----------------+------------+-----------------+----------+-------------+
|               2 | Two        |            NULL |     NULL | NULL        |
|            NULL | NULL       |               4 |        3 | Four        |
+-----------------+------------+-----------------+----------+-------------+
2 rows in set (0.00 sec)

The ORDER BY clause used is a variation on the more common choice of:

  WHEN leftstring = 'One'   OR rightstring = 'One'   THEN 1

The position of two string literals in any statement is a bad idea. Inverting the literal on the right and using a IN operator gives you a better and clearer WHEN statement:

  WHEN 'One'   IN (leftstring, rightstring) THEN 1

As always, I hope this helps those looking for syntax examples.

Written by maclochlainn

June 15th, 2021 at 10:36 pm

Posted in Database Design,MySQL,MySQL 8,sql

Tagged with ,

« Older Entries
Newer Entries »