By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table main_table
(
id varchar(10) not null,
seq varchar(10) not null
)
insert into main_table
(id, seq)
VALUES
('A1', '1'),
('A1', '2'),
('A1', '3'),
('A2', '1'),
('A2', '2'),
('A2', '3'),
('A3', '1'),
('A3', '2'),
('A3', '3');
create table sub_table
(
id varchar(10) not null,
seq varchar(10) not null
)
insert into sub_table
(id, seq)
VALUES
('A1', '1'),
('A1', '2'),
('A2', '1')
;
12 rows affected
SELECT a.id,
a.seq
FROM main_table a
JOIN sub_table b
ON a.id = b.id
GROUP BY a.id,
a.seq
HAVING MAX(CASE WHEN a.seq = b.seq THEN 1 ELSE 0 END) = 0
| id | seq |
|---|---|
| A1 | 3 |
| A2 | 2 |
| A2 | 3 |