Summer self-study day 13 | database (VI) – multi table query

Time:2021-10-22

Inner connection

Implicit inner connection
  • Use the where keyword to eliminate useless data
  • example:
    SELECT
Explicit inner join
  • Select field from table name 1 inner join table name 2 on condition
  • example
    SELECT
      *
    FROM
      emp t1
    INNER JOIN
      dept t2
    ON
      t1.`dept_id` = dept.`id`;

External connection

  • Left outer connection
    • queryLeft tableAll data and its intersection
    • Select field from table 1 left [outer] join table 2 on condition;
  • Right outer connection
    • queryRight tableAll data and its intersection
    • Select field from table 1 right [outer] join table 2 on condition;
  • If the intersection of the queried data and another table is empty, the corresponding column data is displayed as null

Subquery

  • Nested query in query
  • Example: query the information of the employee with the highest salary
    SELECT
      emp.`id`,
      emp.`name`
    FROM
      emp
    WHERE
      emp.`salary` = (SELECT max(salary) FROM emp);
Different sub queries
  • Result single row and single column

    • Look at the example above
  • Result multiple rows and single column

    • Example: query all employee information of finance department and marketing department. Because you want to query two departments, you need to use the in keyword to limit the range of departments
      SELECT
  • Result multiple rows and columns

    • Create a virtual table with sub query to participate in the query

    • Example: query the employee information and department information of employees employed after November 11, 2011

    • Subquery

      SELECT
        * 
      FROM
        dept t1,
        SELECT
            *
        FROM
            emp
        WHERE
            emp.`join_date` > `2011-11-11` t2
      WHERE
        t1.`id`
      INNER JOIN
        t2.`dept_id`;
    • Common internal connection

      SELECT
        *
      FROM
        emp t1,
        dept t2
      WHERE
        t1.`dept_id` = t2.`id`
      AND
        t1.`join_date` > `2011-11-11`;

This work adoptsCC agreement, reprint must indicate the author and the link to this article