mysqlでは試していませんが,
sql
1DROP TABLE IF EXISTS A;
2DROP TABLE IF EXISTS B;
3
4CREATE TABLE A(
5 X varchar(255)
6);
7
8CREATE TABLE B(
9 X varchar(255)
10);
11
12INSERT INTO A (X)
13VALUES
14 ('apple'), ('banana'), ('chocorate'), ('cocoa'), ('tea'), ('coffee'),
15 ('cake'), ('bread');
16
17INSERT INTO B (X)
18VALUES
19 ('desk'), ('chair'), ('bread'), ('cup'), ('cake'), ('grass'),
20 ('mouse'), ('keyboard');
> select * from A;
┌───────────┐
│ X │
│ varchar │
├───────────┤
│ apple │
│ banana │
│ chocorate │
│ cocoa │
│ tea │
│ coffee │
│ cake │
│ bread │
└───────────┘
> select * from B;
┌──────────┐
│ X │
│ varchar │
├──────────┤
│ desk │
│ chair │
│ bread │
│ cup │
│ cake │
│ grass │
│ mouse │
│ keyboard │
└──────────┘
とした時,
sql
1WITH
2 hoge AS (
3 SELECT B.X AS X, A.X AS AX FROM B LEFT OUTER JOIN A ON B.X=A.X
4 )
5SELECT X FROM hoge WHERE AX IS NULL;
という感じでしょうか?