MariaDB 10.2

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

CREATE TABLE members
(
    memberid integer PRIMARY KEY,
    name varchar(100)
) ;
INSERT INTO members
    (memberid, name)
VALUES
    (1, 'member 1'),
    (2, 'member 2') ;
CREATE TABLE fees
(
    memberid integer REFERENCES members(memberid),
    amountpaid decimal(10,2) NOT NULL,
    paiddate date NOT NULL
) ;
INSERT INTO fees
   (memberid, amountpaid, paiddate)
VALUES
   (1, 100.0, curdate()),
   (2, 100.0, curdate() - interval 1 month);
SELECT * FROM fees JOIN members ON fees.memberid = members.memberid;


memberid
amountpaid
paiddate
memberid
name


1
100.00
2017-06-24
1
member 1

2
100.00
2017-05-24
2
member 2


CREATE INDEX idx_member_date ON fees (memberid, paiddate);
SELECT
    members.memberid, members.name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
    LEFT JOIN /* Imperative to be LEFT JOIN */
        fees 
        ON 
            fees.memberid = members.memberid AND
            fees.paiddate BETWEEN DATE_FORMAT(curdate() ,'%Y-%m-01') AND curdate()
WHERE
    fees.memberid IS NULL /* The LEFT JOIN produced nothing on the right side */


memberid
name


2
member 2


explain
SELECT
    members.memberid, members.name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
    LEFT JOIN /* Imperative to be LEFT JOIN */
        fees 
        ON 
            fees.memberid = members.memberid AND
            fees.paiddate BETWEEN DATE_FORMAT(curdate() ,'%Y-%m-01') AND curdate()
WHERE
    fees.memberid IS NULL /* The LEFT JOIN produced nothing on the right side */


id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra


1
SIMPLE
members
ALL
null
null
null
null
2


1
SIMPLE
fees
ref
idx_member_date
idx_member_date
5
fiddle_ZCXLEXEURTHEWEPFKPOV.members.memberid
1
Using where; Using index

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	members	ALL	null	null	null	null	2
1	SIMPLE	fees	ref	idx_member_date	idx_member_date	5	fiddle_ZCXLEXEURTHEWEPFKPOV.members.memberid	1	Using where; Using index