Mysql一简单join和子查询

例子

1. 查询emp_no=110022的员工姓名和部门名称

-- 使用等值连接
SELECT
    C.dept_name,
    A.first_name,
    A.last_name
FROM
    employees A,
    dept_emp B,
    departments C
WHERE
    A.emp_no = B.emp_no
AND B.dept_no = C.dept_no
AND A.emp_no = 110022;
-- 使用join 关联查询
SELECT
    C.dept_name,
    A.first_name,
    A.last_name
FROM
    employees AS A
JOIN dept_emp AS B ON A.emp_no = B.emp_no
AND A.emp_no = 110022
JOIN departments AS C ON B.dept_no = C.dept_no

2. 查询部门名称和这个部门的平均工资

SELECT
    dept_name,
    AVG(salary) AS avg_salary
FROM
    salaries
JOIN dept_emp ON salaries.emp_no = dept_emp.emp_no
JOIN departments ON dept_emp.dept_no = departments.dept_no
GROUP BY
    dept_emp.dept_no
ORDER BY
    avg_salary DESC;

3. 查询除了主键外的重复项

SELECT
    emp1.*
FROM
    employees emp1
JOIN employees emp2 
ON emp1.first_name = emp2.first_name
AND emp1.last_name = emp2.last_name = emp2.last_name
AND emp1.gender = emp2.gender
AND emp1.hire_date = emp2.hire_date
AND emp1.emp_no != emp2.emp_no
ORDER BY
    first_name,
    last_name

4. 查询从 1986-06-26 开始担任高级工程师的员工姓名

-- 子查询
SELECT
    first_name,
    last_name
FROM
    employees
WHERE
    emp_no IN (
        SELECT
            emp_no
        FROM
            titles
        WHERE
            title = 'Senior Engineer'
        AND from_date = '1986-08-26'
    )

-- join连接
SELECT
    first_name,
    last_name
FROM
    employees
JOIN titles ON titles.emp_no = employees.emp_no
AND titles.title = 'Senior Engineer'
AND titles.from_date = '1986-06-26'

5. 查询工资最高的员工的员工号

SELECT
    emp_no
FROM
    salaries
WHERE
    salary = (
        SELECT
            MAX(salary)
        FROM
            salaries
    )

查找表与表之间不匹配的行

1. 先创建两个 emplyess的子表

-- list1
CREATE TABLE employee_list1 AS SELECT
    *
FROM
    employees
WHERE
    first_name LIKE 'aa%';

-- list2
CREATE TABLE employees_list2 AS SELECT
    *
FROM
    employees
WHERE
    emp_no BETWEEN 400000
AND 500000
AND gender = 'F';

• 如果是寻找匹配的行，我们使用的是 JOIN，如果是不匹配的行使用 OUTER JOIN

2. 寻找list1和list2都存在的员工数据

-- 使用子查询
SELECT
    *
FROM
    employees_list1
WHERE
    emp_no IN (
        SELECT
            emp_no
        FROM
            employees_list2
    )

-- 使用JOIN
SELECT
    *
FROM
    employees_list1 AS A
JOIN employees_list2 AS B ON A.emp_no = B.emp_no

3. 寻找存在于list1中而不存在于list2中的员工信息

-- 使用子查询
SELECT
    emp_no
FROM
    employees_list1
WHERE
    emp_no NOT IN (
        SELECT
            emp_no
        FROM
            employees_list2
    )
ORDER BY
    emp_no ASC;

-- 使用LEFT OUTER JOIN
SELECT
    A.emp_no
FROM
    employees_list1 AS A
LEFT OUTER JOIN employees_list2 AS B ON A.emp_no = B.emp_no
WHERE
    B.emp_no IS NULL
ORDER BY A.emp_no asc;

表达式中的类型转换