有如下两条MySQL连接查询语句,结果是完全一样的
上面一条直接在JOIN
关键字后面连接表明,使用ON进行条件匹配,
下面一条在JOIN
中使用SELECT
查询进一步筛选需要的字段并缩小查询范围,
求问这两中写法哪种效率更高,因为数据量比较少,我测试的查询时间不稳定,互有胜负!
SELECT username AS id, zg.F_MC AS name, aq.F_MC AS company_name, enable AS status, zg.F_CRDATE AS create_at, u.last_login_at
FROM t_users u
JOIN cszgzd zg ON zg.F_BH=u.username AND zg.F_SYZT='1'
LEFT JOIN aq_zzjg aq ON aq.F_GSBM=zg.F_SSZZJG AND aq.F_SYZT='1'
SELECT username AS id, zg.name, zg.company_name, enable AS status, zg.F_CRDATE AS create_at, u.last_login_at
FROM t_users u
JOIN (
SELECT zg.F_BH, zg.F_MC AS name, aq.company_name, zg.F_CRDATE
FROM cszgzd zg
LEFT JOIN (SELECT F_GSBM, F_MC as company_name FROM aq_zzjg WHERE F_SYZT='1') aq ON aq.F_GSBM=zg.F_SSZZJG
) zg ON zg.F_BH=u.username