数据库学习纪要（二十六）：MySQL-6-子查询

八、子查询
SELECT mc.first_name, mc.last_name, mc.phone, jc.title FROM job_current AS jc NATURAL JOIN my_contacts AS mc
WHERE jc.title IN (SELECT title FROM job_listings);

子查询和联接对比：
SELECT last_name, first_name FROM my_contacts
WHERE zip_code = (SELECT zip_code FROM zip_code WHERE city = 'Memphis' AND state = 'TN');

SELECT last_name, first_name FROM my_contacts mc
NATURAL JOIN zip_code zc
WHERE zc.city = 'Memphis'
AND zc.state = 'TN';

SELECT mc.first_name, mc.last_name, jc.salary
FROM my_contacts AS mc NATURAL JOIN job_current AS jc
WHERE jc.salary = (SELECT MAX(jc.salary) FROM job_current jc);

子查询搭配自然联接
SELECT mc.first_name, mc.last_name, jc.salary FROM my_contacts AS mc NATURAL JOIN job_current AS jc
WHERE jc.salary > (SELECT jc.salary FROM my_contacts mc NATURAL JOIN job_current jc
WHERE email = '[e-mail]');
//第一个FROM用于获得姓名和薪资
//WHERE jc.salary > 用于查询薪资高于Andy的人
//括号里的子查询语句用于获取Andy的薪资
注：如果子查询可以独立运行且不会引用外层查询的任何结果，即称为非关联子查询

关联子查询
SELECT mc.first_name, mc.last_name FROM my_contacts AS mc
WHERE 3 = (SELECT COUNT(*) FROM contact_interest WHERE contact_id = mc.contact_id);
//本例的子查询中使用了别名（关联名称）mc，是在外层查询中创建的

搭配NOT EXISTS的关联子查询
SELECT mc.first_name firstname, mc.last_name lastname, mc.email email FROM my_contacts mc
WHERE NOT EXISTS
(SELECT * FROM job_current jc WHERE mc.contact_id = jc.contact_id);
//NOT EXISTS：负责从my_contacts表找出姓名何电子邮件地址，他们都未列在job_currnet表中
//若改为EXISTS：负责从my_contacts表找出姓名何电子邮件地址，们都在job_currnet表中

SELECT mc.email FROM my_contacts mc
WHERE
EXISTS
(SELECT * FROM contact_interest ci WHERE mc.contact_id = ci.contact_id)
AND
NOT EXISTS 
(SELECT * FROM job_current jc WHERE mc.contact_id = jc.contact_id);