MySQL 8.0

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

select version();


version()


8.0.30


CREATE TABLE table1 (
  id int auto_increment primary key,
  wert1 int,
  wert2 int,
  wert3 int
);

CREATE TABLE table2  (
  id int auto_increment primary key,
  wert1 int,
  wert2 int,
  wert3 int
);
insert into table1 (wert1, wert2, wert3)
select round(rand()*3,0),round(rand()*3,0),round(rand()*3,0) 
  FROM information_schema.tables limit 10;
insert into table2 (wert1, wert2, wert3)
select round(rand()*3,0),round(rand()*3,0),round(rand()*3,0) 
  FROM information_schema.tables limit 20;

Records: 10  Duplicates: 0  Warnings: 0



Records: 20  Duplicates: 0  Warnings: 0


-- alle passenden Werte
--     den * im Select Statement nutze ich nur aus Bequemlichkeit in diesem Beispiel,
--     nicht in einem Produktivsystem
select * from table1 t1 join table2 t2
    on t1.wert1 = t2.wert1
   and t1.wert2 = t2.wert2
   and t1.wert3 = t2.wert3;


id
wert1
wert2
wert3
id
wert1
wert2
wert3


1
2
1
1
9
2
1
1

2
0
0
1
10
0
0
1


-- alle NICHT passenden Werte
select * from table1 t1 left join table2 t2
    on t1.wert1 = t2.wert1
   and t1.wert2 = t2.wert2
   and t1.wert3 = t2.wert3
 where t2.id is null;


id
wert1
wert2
wert3
id
wert1
wert2
wert3


3
3
0
2
null
null
null
null

4
1
0
3
null
null
null
null

5
3
0
2
null
null
null
null

6
0
1
3
null
null
null
null

7
1
1
3
null
null
null
null

8
3
1
1
null
null
null
null

9
1
0
2
null
null
null
null

10
2
3
1
null
null
null
null


-- alle NICHT passenden Werte in ELEGANT
select * 
  from table1 t1 left join table2 t2
    on (t1.wert1, t1.wert2, t1.wert3) 
     = (t2.wert1, t2.wert2, t2.wert3)
 where t2.id is null;


id
wert1
wert2
wert3
id
wert1
wert2
wert3


3
3
0
2
null
null
null
null

4
1
0
3
null
null
null
null

5
3
0
2
null
null
null
null

6
0
1
3
null
null
null
null

7
1
1
3
null
null
null
null

8
3
1
1
null
null
null
null

9
1
0
2
null
null
null
null

10
2
3
1
null
null
null
null

id	wert1	wert2	wert3	id	wert1	wert2	wert3
3	3	0	2	null	null	null	null
4	1	0	3	null	null	null	null
5	3	0	2	null	null	null	null
6	0	1	3	null	null	null	null
7	1	1	3	null	null	null	null
8	3	1	1	null	null	null	null
9	1	0	2	null	null	null	null
10	2	3	1	null	null	null	null

id	wert1	wert2	wert3	id	wert1	wert2	wert3
3	3	0	2	null	null	null	null
4	1	0	3	null	null	null	null
5	3	0	2	null	null	null	null
6	0	1	3	null	null	null	null
7	1	1	3	null	null	null	null
8	3	1	1	null	null	null	null
9	1	0	2	null	null	null	null
10	2	3	1	null	null	null	null