SQL – Select查询的多种方法

    CREATE TABLE tbl_students

    (

        snumber char,

        sname varchar(10)

    )

 

    CREATE TABLE tbl_curriculums

    (

        cnumber char,

        cname varchar(15),

        cteacher varchar(10)

    )

 

    CREATE TABLE tbl_relationships

    (

        snumber char,

        cnumber char,

        score varchar(3)

    )

    --Method 1 Good

SELECT S.sname

FROM

    tbl_students S,

    tbl_curriculums C,

    tbl_relationships R

WHERE

    S.snumber = R.snumber AND

    R.cnumber = C.cnumber AND

    C.cteacher = 'XU'

 

--Method 2 Better

SELECT S.sname

FROM

tbl_students S

WHERE

S.snumber in

(

    SELECT R.snumber

    From

        tbl_curriculums C,

        tbl_relationships R

    WHERE

        R.cnumber = C.cnumber AND

        C.cteacher = 'XU'

)

 

-- Method 3 Best

SELECT S.sname

FROM

tbl_students S

INNER JOIN tbl_relationships R ON S.snumber = R.snumber

INNER JOIN tbl_curriculums C ON R.cnumber = C.cnumber

AND C.cteacher ='XU'