我正在尝试获取查询的结果：
SELECT product, exportcountry
FROM is_exported_to CROSS JOIN is_produced_in
WHERE product = cheese AND origin = 'France';但我似乎无法得到任何回报。
两个 tables 创建如下：
CREATE TABLE is_produced_in(
cheese VARCHAR(32),
origin VARCHAR(32)
);
INSERT INTO is_produced_in (cheese, origin)
VALUES
('Brie', 'France'),
('Chrur', 'Tibet'),
('Halloumi', 'Greece'),
('Orgu', 'Turkey'),
('Salers', 'France'),
('Serrano', 'Brazil')
CREATE TABLE is_exported_to(
product VARCHAR(32),
exportcountry VARCHAR(32)
);
INSERT INTO is_exported_to (product, exportcountry)
VALUES
('Brie', 'Greece'),
('Halloumi', 'Brazil'),
('Halloumi', 'France'),
('Halloumi', 'Tibet'),
('Orgu', 'France'),
('Orgu', 'Tibet'),
('Salers', 'Greece'),
('Serrano', 'Tibet'),
('Serrano', 'Turkey')回答1
你错过了“;”在你的初始化脚本中。
CREATE TABLE is_produced_in(
cheese VARCHAR(32),
origin VARCHAR(32)
);
INSERT INTO is_produced_in (cheese, origin)
VALUES
('Brie', 'France'),
('Chrur', 'Tibet'),
('Halloumi', 'Greece'),
('Orgu', 'Turkey'),
('Salers', 'France'),
('Serrano', 'Brazil');
CREATE TABLE is_exported_to(
product VARCHAR(32),
exportcountry VARCHAR(32)
);
INSERT INTO is_exported_to (product, exportcountry)
VALUES
('Brie', 'Greece'),
('Halloumi', 'Brazil'),
('Halloumi', 'France'),
('Halloumi', 'Tibet'),
('Orgu', 'France'),
('Orgu', 'Tibet'),
('Salers', 'Greece'),
('Serrano', 'Tibet'),
('Serrano', 'Turkey');查询 sql 可以是：
SELECT product, exportcountry
FROM is_exported_to CROSS JOIN is_produced_in
ON product = cheese AND origin = 'France';使用“ON”而不是“WHERE”
回答2
不需要在交叉join 中指定join 条件。您可以使用以下查询：
SELECT * from is_exported_to
CROSS JOIN is_produced_in
WHERE origin='France'