Syntax `WHERE NOT EXISTS` using MySQL

Question

This is my table tb_group on DB MySQL

_tdate	_tgroup	_tnumber
2025-05-12	71AB5	1
2025-05-12	71DB1	1

The values of columns _tdate and _tnumber of the table tb_group are obtained from a table where accesses are recorded for individual groups, therefore in column _tdate you find the access date, in column _tnumber you find the number of accesses that occurred with that group…

To find missing groups in the table tb_group I use the syntax WHERE NOT EXISTS by crossing the values of the _tgroup columns of the two tables ta_group and tb_group.

SELECT
    `_tgroup` 
FROM
    `ta_group` q 
WHERE
    NOT EXISTS ( SELECT 1 FROM `tb_group` t WHERE q.`_tgroup` = t.`_tgroup` );

This is the result

_tgroup
71AB3
71AB4
71AB6
71DB2
71DB4
71DB6

Now I need insert into my table tb_group the values of the _tgroup missing in the table tb_group for this output

_tdate	_tgroup	_tnumber
2025-05-12	71AB3	0
2025-05-12	71AB4	0
2025-05-12	71AB5	1
2025-05-12	71AB6	0
2025-05-12	71DB1	1
2025-05-12	71DB2	0
2025-05-12	71DB4	0
2025-05-12	71DB6	0

I have tried this solution fiddle demo

I have this problem:

the date is not just the day 2025-05-12

In my simplified example I only reported the data for the day 2025-05-12 but in the tb_group table there are all the days of the year up to yesterday...

I have to insert into the tb_group table all the groups that are missing in the tb_group table, taking them from the ta_group table, for each day recorded in the tb_group table

Solution fiddle demo

Guillaume Outters · Accepted Answer · 2025-05-22 10:09:10Z

3

To achieve your goal of inserting missing _tgroup values from ta_group into tb_group for every distinct _tdate present in tb_group, you'll need to generate a cross join between all dates and all groups, then filter out those already existing in tb_group.

INSERT INTO tb_group (_tdate, _tgroup, _tnumber)
SELECT 
    d._tdate,
    g._tgroup,
    0 AS _tnumber
FROM 
    (SELECT DISTINCT _tdate FROM tb_group) d
CROSS JOIN 
    ta_group g
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM tb_group t 
        WHERE t._tdate = d._tdate AND t._tgroup = g._tgroup
    );

Explanation:

(SELECT DISTINCT _tdate FROM tb_group): Gets all unique dates from your existing table.
CROSS JOIN ta_group: Pairs each date with every group in ta_group, forming all possible combinations.
NOT EXISTS: Ensures you only insert combinations that don't already exist in tb_group.
_tnumber = 0: You insert 0 for missing records as required.

📌 Example:

Given:
- Dates in tb_group: 2025-05-12, 2025-05-13
- Groups in ta_group: 71AB1, 71AB2, 71AB3
The query will check for every combination like:
2025-05-12, 71AB1
2025-05-12, 71AB2
2025-05-13, 71AB1
…and so on.
It will only insert combinations that are not already present in tb_group.

edited May 22 at 10:09

Guillaume Outters

7,5371 gold badge22 silver badges28 bronze badges

answered May 22 at 9:51

anuraj3690

463 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Akina May 22 at 10:09

INSERT IGNORE is more clear, fast and simple than WHERE NOT EXISTS.

Akina · Accepted Answer · 2025-05-22 10:04:33Z

3

INSERT IGNORE INTO tb_group
SELECT dateslist._tdate, ta_group._tgroup, 0
FROM ta_group
CROSS JOIN (
  SELECT DISTINCT _tdate
  FROM tb_group
  ) dateslist;
SELECT * FROM tb_group ORDER BY 1, 2;

fiddle

CROSS JOIN generates all (group-date) pairs. INSERT IGNORE inserts only those pairs which are not present in the table yet.

If ta_group._tgroup is not unique then replace the table in the query with the subquery which selects only distinct group codes values.

answered May 22 at 10:04

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

Syntax `WHERE NOT EXISTS` using MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related