clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335579 fiddles created (27417 in the last week).

CREATE TABLE members ( memberid integer PRIMARY KEY, name varchar(100) ) ;
 hidden batch(es)


INSERT INTO members (memberid, name) VALUES (1, 'member 1'), (2, 'member 2') ;
 hidden batch(es)


CREATE TABLE fees ( memberid integer REFERENCES members(memberid), amountpaid decimal(10,2) NOT NULL, paiddate date NOT NULL ) ;
 hidden batch(es)


INSERT INTO fees (memberid, amountpaid, paiddate) VALUES (1, 100.0, curdate()), (2, 100.0, curdate() - interval 1 month);
 hidden batch(es)


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
 hidden batch(es)


CREATE INDEX idx_member_date ON fees (memberid, paiddate);
 hidden batch(es)


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
 hidden batch(es)


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 2
1 SIMPLE fees ref idx_member_date idx_member_date 5 fiddle_ZCXLEXEURTHEWEPFKPOV.members.memberid 1 Using where; Using index
 hidden batch(es)