MySQL 5.7

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

select version();


version()


5.7.35


SET SESSION sql_mode = CONCAT(@@session.sql_mode, ',', 'ONLY_FULL_GROUP_BY');
CREATE TABLE store_customers (
   autoinc     int(10) unsigned NOT NULL AUTO_INCREMENT,
   store_id    varchar(50) NOT NULL,
   customer_id varchar(50) NOT NULL,
   datetime    datetime    NOT NULL,
   field_1     varchar(50) DEFAULT NULL,
   field_2     varchar(50) DEFAULT NULL,
   PRIMARY KEY (autoinc),
   UNIQUE KEY store_id (store_id),
   KEY customer_id (customer_id)
);
INSERT INTO store_customers (store_id, customer_id, datetime, field_1, field_2)
VALUES ('100', '10', '2011-01-01', 'aaa', 'bbb'),
       ('200', '20', '2012-01-01', 'ccc', 'ddd'),
       ('300', '20', '2013-01-01', 'eee', 'fff'),
       ('400', '40', '2014-01-01', 'ggg', 'hhh'),
       ('500', '50', '2015-01-01', 'iii', 'jjj'),
       ('600', '50', '2016-01-01', 'kkk', 'lll'),
       ('700', '70', '2017-01-01', 'mmm', 'nnn'),
       ('800', '70', '2018-01-01', 'ooo', 'ppp'),
       ('900', '90', '2019-01-01', 'qqq', 'rrr');

CREATE TABLE additional_customers (
   store_id    varchar(50) NOT NULL,
   customer_id varchar(50) NOT NULL
);

INSERT INTO store_customers (store_id, customer_id)
VALUES ('400', '41'),
       ('400', '42'),
       ('500', '51'),
       ('500', '52'),
       ('700', '71'),
       ('700', '72'),
       ('800', '81'),
       ('800', '82'),
       ('900', '70');
       
SELECT customer_id FROM store_customers
 UNION
SELECT customer_id FROM additional_customers;


customer_id


10

20

40

50

70

90


SELECT customers.customer_id, MIN(store_customers.datetime) first_datetime
  FROM store_customers 
  JOIN (
          SELECT customer_id FROM store_customers
           UNION
          SELECT customer_id FROM additional_customers
       ) customers ON store_customers.customer_id = customers.customer_id
 GROUP BY customers.customer_id


customer_id
first_datetime


10
2011-01-01 00:00:00

20
2012-01-01 00:00:00

40
2014-01-01 00:00:00

50
2015-01-01 00:00:00

70
2017-01-01 00:00:00

90
2019-01-01 00:00:00


SELECT store_customers.*
  FROM store_customers
  JOIN (
           SELECT customers.customer_id, MIN(store_customers.datetime) first_datetime
             FROM store_customers 
             JOIN (
                    SELECT customer_id FROM store_customers
                    UNION
                    SELECT customer_id FROM additional_customers
                  ) customers ON store_customers.customer_id = customers.customer_id
           GROUP BY customers.customer_id
       ) firsts ON store_customers.customer_id = firsts.customer_id
               AND store_customers.datetime = firsts.first_datetime


autoinc
store_id
customer_id
datetime
field_1
field_2


1
100
10
2011-01-01 00:00:00
aaa
bbb

2
200
20
2012-01-01 00:00:00
ccc
ddd

4
400
40
2014-01-01 00:00:00
ggg
hhh

5
500
50
2015-01-01 00:00:00
iii
jjj

7
700
70
2017-01-01 00:00:00
mmm
nnn

9
900
90
2019-01-01 00:00:00
qqq
rrr


EXPLAIN
SELECT store_customers.*
  FROM store_customers
  JOIN (
           SELECT customers.customer_id, MIN(store_customers.datetime) first_datetime
             FROM store_customers 
             JOIN (
                    SELECT customer_id FROM store_customers
                    UNION
                    SELECT customer_id FROM additional_customers
                  ) customers ON store_customers.customer_id = customers.customer_id
           GROUP BY customers.customer_id
       ) firsts ON store_customers.customer_id = firsts.customer_id
               AND store_customers.datetime = firsts.first_datetime


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
store_customers
null
ALL
customer_id
null
null
null
9
100.00
null

1
PRIMARY
<derived2>
null
ref
<auto_key0>
<auto_key0>
58
db_1848834015.store_customers.customer_id,db_1848834015.store_customers.datetime
2
100.00
Using index

2
DERIVED
store_customers
null
ALL
customer_id
null
null
null
9
100.00
Using temporary; Using filesort

2
DERIVED
<derived3>
null
ref
<auto_key0>
<auto_key0>
52
db_1848834015.store_customers.customer_id
2
100.00
Using index

3
DERIVED
store_customers
null
index
null
customer_id
52
null
9
100.00
Using index

4
UNION
additional_customers
null
ALL
null
null
null
null
1
100.00
null

null
UNION RESULT
<union3,4>
null
ALL
null
null
null
null
null
null
Using temporary


ALTER TABLE store_customers
  DROP KEY customer_id,
  ADD KEY cust_date (customer_id, datetime DESC);
EXPLAIN
SELECT store_customers.*
  FROM store_customers
  JOIN (
           SELECT customers.customer_id, MIN(store_customers.datetime) first_datetime
             FROM store_customers 
             JOIN (
                    SELECT customer_id FROM store_customers
                    UNION
                    SELECT customer_id FROM additional_customers
                  ) customers ON store_customers.customer_id = customers.customer_id
           GROUP BY customers.customer_id
       ) firsts ON store_customers.customer_id = firsts.customer_id
               AND store_customers.datetime = firsts.first_datetime


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
store_customers
null
ALL
cust_date
null
null
null
9
100.00
null

1
PRIMARY
<derived2>
null
ref
<auto_key0>
<auto_key0>
58
db_1848834015.store_customers.customer_id,db_1848834015.store_customers.datetime
2
100.00
Using index

2
DERIVED
store_customers
null
index
cust_date
cust_date
57
null
9
100.00
Using index; Using temporary; Using filesort

2
DERIVED
<derived3>
null
ref
<auto_key0>
<auto_key0>
52
db_1848834015.store_customers.customer_id
2
100.00
Using index

3
DERIVED
store_customers
null
index
null
cust_date
57
null
9
100.00
Using index

4
UNION
additional_customers
null
ALL
null
null
null
null
1
100.00
null

null
UNION RESULT
<union3,4>
null
ALL
null
null
null
null
null
null
Using temporary


SELECT   ANY_VALUE(sc.store_id),
         customers.customer_id,
         MIN(sc.datetime),
         ANY_VALUE(sc.field_1),
         ANY_VALUE(sc.field_2)
FROM     (
           SELECT store_id, customer_id
           FROM   store_customers
           UNION ALL
           SELECT store_id, customer_id
           FROM   additional_customers
         ) AS customers
JOIN     store_customers sc
         ON sc.store_id    = customers.store_id
         OR sc.customer_id = customers.customer_id
GROUP BY customers.customer_id;


ANY_VALUE(sc.store_id)
customer_id
MIN(sc.datetime)
ANY_VALUE(sc.field_1)
ANY_VALUE(sc.field_2)


100
10
2011-01-01 00:00:00
aaa
bbb

200
20
2012-01-01 00:00:00
ccc
ddd

400
40
2014-01-01 00:00:00
ggg
hhh

500
50
2015-01-01 00:00:00
iii
jjj

700
70
2017-01-01 00:00:00
mmm
nnn

900
90
2019-01-01 00:00:00
qqq
rrr

customer_id	first_datetime
10	2011-01-01 00:00:00
20	2012-01-01 00:00:00
40	2014-01-01 00:00:00
50	2015-01-01 00:00:00
70	2017-01-01 00:00:00
90	2019-01-01 00:00:00

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	store_customers	null	ALL	customer_id	null	null	null	9	100.00	null
1	PRIMARY	<derived2>	null	ref	<auto_key0>	<auto_key0>	58	db_1848834015.store_customers.customer_id,db_1848834015.store_customers.datetime	2	100.00	Using index
2	DERIVED	store_customers	null	ALL	customer_id	null	null	null	9	100.00	Using temporary; Using filesort
2	DERIVED	<derived3>	null	ref	<auto_key0>	<auto_key0>	52	db_1848834015.store_customers.customer_id	2	100.00	Using index
3	DERIVED	store_customers	null	index	null	customer_id	52	null	9	100.00	Using index
4	UNION	additional_customers	null	ALL	null	null	null	null	1	100.00	null
null	UNION RESULT	<union3,4>	null	ALL	null	null	null	null	null	null	Using temporary