如何在sql中使用一个表作为另一个表的过滤器并生成一个新表

CREATE TABLE A (
  id INT,
  name VARCHAR(50)
);

CREATE TABLE B (
  id INT,
  value INT
);

INSERT INTO A (id, name) VALUES (1, 'John');
INSERT INTO A (id, name) VALUES (2, 'Jane');
INSERT INTO A (id, name) VALUES (3, 'Mike');

INSERT INTO B (id, value) VALUES (1, 10);
INSERT INTO B (id, value) VALUES (2, 20);
INSERT INTO B (id, value) VALUES (3, 30);
INSERT INTO B (id, value) VALUES (4, 40);

-- 使用表A作为过滤器生成新表C
CREATE TABLE C AS
SELECT B.id, B.value
FROM B
WHERE B.id IN (SELECT id FROM A);

-- 查看新表C的内容
SELECT * FROM C;