MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE CLERK (
  CLERK_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL
);

CREATE TABLE CUSTOMER (
  CUSTOMER_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  NAME VARCHAR(10) NOT NULL,
  CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL,
  CLERK_CI_AI CHAR(3) CHARACTER SET latin1 COLLATE latin1_general_ci NULL
);

INSERT INTO CUSTOMER (NAME, CLERK, CLERK_CI_AI) VALUES('John Doe', 'Ü', 'Ü');

INSERT INTO CLERK (CLERK) VALUES('Ü');
INSERT INTO CLERK (CLERK) VALUES('ü');
INSERT INTO CLERK (CLERK) VALUES('U');
INSERT INTO CLERK (CLERK) VALUES('u');
INSERT INTO CLERK (CLERK) VALUES('Ù');
INSERT INTO CLERK (CLERK) VALUES('ù');
INSERT INTO CLERK (CLERK) VALUES('Û');
INSERT INTO CLERK (CLERK) VALUES('û');

INSERT INTO CLERK (CLERK) VALUES('Y');
INSERT INTO CLERK (CLERK) VALUES('y');
INSERT INTO CLERK (CLERK) VALUES('Ý');
INSERT INTO CLERK (CLERK) VALUES('ý');
SELECT 0 AS "ver", C.*
FROM   CLERK C;


ver
CLERK_ID
CLERK


0
1
Ü

0
2
ü

0
3
U

0
4
u

0
5
Ù

0
6
ù

0
7
Û

0
8
û

0
9
Y

0
10
y

0
11
Ý

0
12
ý


SELECT "bin sort" AS "ver", C.*
FROM   CLERK C
ORDER BY C.CLERK;


ver
CLERK_ID
CLERK


bin sort
3
U

bin sort
9
Y

bin sort
4
u

bin sort
10
y

bin sort
5
Ù

bin sort
7
Û

bin sort
1
Ü

bin sort
11
Ý

bin sort
6
ù

bin sort
8
û

bin sort
2
ü

bin sort
12
ý


SELECT "cs sort" AS "ver", C.*
FROM   CLERK C
ORDER BY C.CLERK COLLATE latin1_general_cs;


ver
CLERK_ID
CLERK


cs sort
3
U

cs sort
4
u

cs sort
5
Ù

cs sort
6
ù

cs sort
7
Û

cs sort
8
û

cs sort
1
Ü

cs sort
2
ü

cs sort
9
Y

cs sort
10
y

cs sort
11
Ý

cs sort
12
ý


SELECT 1 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK = C.CLERK;


ver
CUSTOMER_ID
NAME
CLERK
CLERK_CI_AI
CLERK_ID
CLERK


1
1
John Doe
Ü
Ü
1
Ü


SELECT 2 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK = C.CLERK COLLATE latin1_bin;


ver
CUSTOMER_ID
NAME
CLERK
CLERK_CI_AI
CLERK_ID
CLERK


2
1
John Doe
Ü
Ü
1
Ü


SELECT 3 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK_CI_AI = C.CLERK;


ver
CUSTOMER_ID
NAME
CLERK
CLERK_CI_AI
CLERK_ID
CLERK


3
1
John Doe
Ü
Ü
1
Ü


SELECT 4 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK_CI_AI = C.CLERK COLLATE latin1_bin;


ver
CUSTOMER_ID
NAME
CLERK
CLERK_CI_AI
CLERK_ID
CLERK


4
1
John Doe
Ü
Ü
1
Ü


# is accent-sensitive even though documentation states it should be
# accent-INsensitive
# 
SELECT 5 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK_CI_AI = C.CLERK COLLATE latin1_general_ci;


ver
CUSTOMER_ID
NAME
CLERK
CLERK_CI_AI
CLERK_ID
CLERK


5
1
John Doe
Ü
Ü
1
Ü

5
1
John Doe
Ü
Ü
2
ü


SELECT 6 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON CONVERT(S.CLERK_CI_AI using utf8mb4) = CONVERT(C.CLERK USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;


ver
CUSTOMER_ID
NAME
CLERK
CLERK_CI_AI
CLERK_ID
CLERK


6
1
John Doe
Ü
Ü
1
Ü

6
1
John Doe
Ü
Ü
2
ü

6
1
John Doe
Ü
Ü
3
U

6
1
John Doe
Ü
Ü
4
u

6
1
John Doe
Ü
Ü
5
Ù

6
1
John Doe
Ü
Ü
6
ù

6
1
John Doe
Ü
Ü
7
Û

6
1
John Doe
Ü
Ü
8
û

ver	CLERK_ID	CLERK
bin sort	3	U
bin sort	9	Y
bin sort	4	u
bin sort	10	y
bin sort	5	Ù
bin sort	7	Û
bin sort	1	Ü
bin sort	11	Ý
bin sort	6	ù
bin sort	8	û
bin sort	2	ü
bin sort	12	ý

ver	CLERK_ID	CLERK
cs sort	3	U
cs sort	4	u
cs sort	5	Ù
cs sort	6	ù
cs sort	7	Û
cs sort	8	û
cs sort	1	Ü
cs sort	2	ü
cs sort	9	Y
cs sort	10	y
cs sort	11	Ý
cs sort	12	ý

ver	CUSTOMER_ID	NAME	CLERK	CLERK_CI_AI	CLERK_ID	CLERK
6	1	John Doe	Ü	Ü	1	Ü
6	1	John Doe	Ü	Ü	2	ü
6	1	John Doe	Ü	Ü	3	U
6	1	John Doe	Ü	Ü	4	u
6	1	John Doe	Ü	Ü	5	Ù
6	1	John Doe	Ü	Ü	6	ù
6	1	John Doe	Ü	Ü	7	Û
6	1	John Doe	Ü	Ü	8	û