inner join 使用场景

对于同一个条件要求满足同时不同的值查询情景

--查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT
    st.* 
FROM
    student st
    INNER JOIN score sc ON sc.s_id = st.s_id
    INNER JOIN course c ON c.c_id = sc.c_id 
    AND c.c_id = "01" 
WHERE
    st.s_id IN (
SELECT
    st2.s_id 
FROM
    student st2
    INNER JOIN score sc2 ON sc2.s_id = st2.s_id
    INNER JOIN course c2 ON c2.c_id = sc2.c_id 
    AND c2.c_id = "02" 
    )