失われたレコードの検索

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS LEFT JOIN ANIMAL_INS INS ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID

JOIN:2つのテーブルをマージ
問題は、JOIN以降INSテーブルにないコンテンツIDがNULLである.WHEREにIS NULLと書いてあると、OUTSのうちINSにない値しか出力できません.

いいえ。

SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS AS INS JOIN ANIMAL_OUTS AS OUTS
WHERE INS.ANIMAL_ID = OUTS.ANIMAL_ID AND OUTS.DATETIME <= INS.DATETIME
ORDER BY INS.DATETIME

長期保護動物(1)

📌 JOIN VS LEFT JOIN

JOIN ( INNER JOIN )

:Table AとTable Bの交差を検索する

LEFT JOIN ( LEFT OUTER JOIN )

:Table AとTable Bの並列セットを検索する

SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS INS LEFT JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL
ORDER BY INS.DATETIME
LIMIT 3