如何选择不参与连接的记录

我需要创建一个返回一些不参与连接的记录的查询,我粘贴所涉及的表和我写的查询.

表格为CAR和SPACE,关系为"一对多"(N个空间中1个CAR; 1个SPACE 1 CAR中)

汽车表记录:

........................................
car_id  | mark    | model   | prize
........................................
    1   | fiat    | 500     | 15000
    2   | fiat    | panda   | 8000
    3   | opel    | astra   | 20000
    4   | renault | clio    | 14000
    5   | opel    | corsa   | 12000
    6   | fiat    | panda   | 8000
    7   | fiat    | 500     | 15000
    8   | renault | capture | 16000
    9   | renault | clio    | 14000
    10  | fiat    | 500     | 15000

SPACE TABLE的记录(主键由row,column和room_id标识.car_id是CAR表的主键的外键):

..................................................
   row  | column  | room_id | dimension | car_id
..................................................
    1   | 1       | 1       | 100       | 1
    1   | 2       | 1       | 100       | 1
    1   | 3       | 1       | 100       | NULL
    2   | 1       | 1       | 100       | 1
    2   | 2       | 1       | 100       | 1
    2   | 3       | 1       | 100       | NULL
    3   | 1       | 1       | 100       | NULL
    3   | 2       | 1       | 100       | 6
    3   | 3       | 1       | 100       | 6

现在,我想从查询中得到的是选择所有不在任何空间的汽车.我希望结果是那个表:

........................................
car_id  | mark    | model   | prize
........................................
    2   | fiat    | panda   | 8000
    3   | opel    | astra   | 20000
    4   | renault | clio    | 14000
    5   | opel    | corsa   | 12000
    7   | fiat    | 500     | 15000
    8   | renault | capture | 16000
    9   | renault | clio    | 14000
    10  | fiat    | 500     | 15000

这是我写的查询,但它没有返回我期望的结果.

SELECT car.* FROM car,space WHERE car.car_id = space.car_id AND space.car_id IS NULL

我最近才学习数据库的逻辑,不幸的是我不好,查询完全错误,但我不知道如何纠正它.

您在条款中使用了冲突的术语where:

WHERE car.car_id = space.car_id AND space.car_id IS NULL

但space.car_id不能同时null匹配car.car_id.

您正在寻找左连接:

SELECT  *
FROM    car
LEFT JOIN
        space
ON      car.car_id = space.car_id
WHERE   space.car_id IS NULL