如果列不存在，请加入两个表并在列中显示null

Question

I have two tables as follows.

stages:

stageid     stagename     is_corrected
   1           abc              1
   2           xyz              1
   3           aaa              0
   4           bbb              1

responses:

stageid     teamid      diffscore      
   1           1            10
   1           2            12
   1           3            15
   2           1            12
   2           2            13
   2           3            16
   2           4            14

I am trying to join them and show a joined table where is_corrected = 1. Here is the query I tried:

Query:

SELECT 
    t1.stagename, 
    t2.diffscore 
FROM 
    stages t1 
    LEFT OUTER JOIN (
        SELECT 
            diffscore, 
            teamid, 
            stageid 
        FROM 
            responses as t2
    ) as t2 ON t2.stageid = t1.stageid 
    OR t1.stageid = NULL 
WHERE 
    t1.is_corrected = 1 
    AND (
        t2.teamid = 4 
        OR t2.teamid = NULL
    )

Expected Result:

stagename      diffscore
   abc            NULL
   xyz            14
   bbb            NULL

Output:

stagename      diffscore
   xyz            14

Answer 1

Try this:

SELECT 
    s.stageid, 
    s.stagename, 
    IFNULL(
        (
            SELECT 
                diffscore 
            FROM 
                responses 
            WHERE 
                s.stageid = stageid 
                AND (
                    teamid = 1
                    OR teamid IS NULL
                )
            LIMIT 1
        ), 
        0
    ) AS diffscore 
FROM 
    stages s 
WHERE 
    is_corrected = 1