MySQL从table01中选择table02上不存在的行[重复]

我有两个表,table03有10行,table01有21行,现在我想从table03中获取行,而table01中不存在这些行,到目前为止,我编写了此查询,但它显示了table03的所有行,甚至有些行也没有。 t存在于table01上。

SELECT T3.`DateAdded`, T3.`Query_name`, T3.`Fund`, T3.`Ticker` 
FROM `table_name03` T3 LEFT JOIN `table_name01` T1 
ON T3.`DateAdded` = T1.`DateAdded` 
AND `T3`.`Query_name` = `T1`.`Query_name` 
AND `T3`.`Fund` = `T1`.`Fund`
AND `T3`.`Ticker` = `T1`.`Ticker`

Table03: enter image description here

Table01: enter image description here

评论
  • 菊Oo
    菊Oo 回复

    You are on the right track. You can just add a where condition to filter on unmatched rows:

    SELECT T3.DateAdded, T3.Query_name, T3.Fund, T3.Ticker 
    FROM table_name03 T3 
    LEFT JOIN table_name01 T1 
        ON  T3.DateAdded = T1.DateAdded 
        AND T3.Query_name = T1.Query_name 
        AND T3.Fund = T1.Fund
        AND T3.Ticker = T1.Ticker
    WHERE T1.DateAdded IS NULL
    

    You can also use not exists:

    SELECT T3.DateAdded, T3.Query_name, T3.Fund, T3.Ticker 
    FROM table_name03 T3 
    WHERE NOT EXISTS (
        SELECT 1
        FROM table_name01 T1 
        WHERE
            T3.DateAdded = T1.DateAdded 
            AND T3.Query_name = T1.Query_name 
            AND T3.Fund = T1.Fund
            AND T3.Ticker = T1.Ticker
    )