你如何加入两个MySQL表，其中数据不在另一个表中？

For example, I have a pet table and a lost pets table. I want to get all of the pets that are NOT lost with 1 join and no sub-selects. Is that possible? A typical join only returns results that are in both tables.

例如,我有一张宠物桌和丢失的宠物桌。我希望得到所有不会因1次加入而没有丢失的宠物。那可能吗?典型的连接仅返回两个表中的结果。

4 个解决方案

#1

You're describing an OUTER JOIN as compared to a standard INNER JOIN. Google or check your documentation - I'm sure you'll find lots of examples. :)

与标准的INNER JOIN相比,您正在描述OUTER JOIN。谷歌或检查你的文件 - 我相信你会找到很多例子。 :)

SELECT * FROM pets AS p
LEFT OUTER JOIN lost-pets AS lp
ON p.name = lp.name
WHERE lp.id IS NULL

SELECT * FROM pets AS p LEFT OUTER JOIN lost-pets AS lp ON p.name = lp.name WHERE lp.id IS NULL

#2

SELECT PETS.NAME
FROM PETS
   LEFT OUTER JOIN LOST_PETS
     ON PETS.PET_ID = LOST_PETS.PET_ID
WHERE LOST_PETS.PET_ID IS NULL;

#3

It is possible, yes, say :

有可能,是的,说:

SELECT *
FROM pets LEFT OUTER JOIN pets-lost ON pets.id = pets-lost.id
WHERE pets-lost.id IS NULL;

#4

-1

Why not do where not exists (select * from Lost ...)? Its a sub-select, but I don't see why thats a problem.

为什么不在不存在的地方做(从迷失中选择*)?它是一个子选择,但我不明白为什么这是一个问题。

#1