Analysis of connection query examples in MySQL

1. Cartesian product

Table 1 has m rows of data, Table 2 has n rows of data, and the query result has m*n rows of data.

2. Classification

(1) Classification by age

sql92 standard: only supports inner joins

sql99 standard (recommended): supports inner joins, Outer joins (left outer joins and right outer joins), cross joins

(2) Classification by function

Inner joins: equivalent joins, non-equivalent joins, self-joins

Outer join: left outer join, right outer join, full outer join

Cross join

3. Equivalent join

(1) Query the girl’s name and its corresponding Boyfriend’s name

SELECT 
    girlname,
    boyname
FROM
    boys,
    girls
WHERE
    girls.boyfriend_id=boys.girlfriend_id;

(2) Query the employee name (last_name) and the corresponding department name (department_name)------------- (query based on the associated id)

SELECT 
    last_name,
    department_name
FROM
    JDSC,
    SNSC
WHERE
    JDSC.`id`=SNSC.`id`;

(3) Query employee name (last_name), job type number (job_id), job type name (job_title) (employee table: JDSC work table: JOBSC) ------ Alias ​​the table to improve simplicity , to avoid ambiguity

#"e.job_id"是为了避免歧义
SELECT
    last_name,
    e.job_id,
    job_title
FROM
    ESC e,
    JOBSC j
WHERE
    e.`job_id`=j.`job_id`;

Note:

If an alias is given to the table, the queried fields cannot be qualified by the original table name.

The order of the above two tables can be interchanged

(4) Query the names of employees and departments with bonuses

#员工名:last_name
#部门名:department_name
#奖金率:commissiom_pct
SELECT
    last_name,
    department_name,
    commissiom_pct
FROM
    employees e,
    department d
WHERE
    e.`department_id`=d.`department_id`
AND
    e.`commissiom_pct` IS NOT NULL;

(5) Group query-mdash;Query Number of departments in each city

#城市分组表名:city
#部门分组表名:departments 
SELECT
    COUNT(*) 个数,
    city
FROM
    departments d,
    locations l
WHERE
    d.`location_id`=l.`location_id`
GROUP BY
    city;

(6) Sorting - Query the job name and number of employees for each type of work, and sort them in descending order by the number of employees

SELECT 
    job_title,
    COUNT(*)
FROM
    emloyees e,
    jobs j
WHERE
    e.`job_id`=j.`job_id`
GROUP BY
    job_title
ORDER BY
    COUNT(*) DESC;

(7) Three Table connection-query employee name, department name and city

SELECT
    last_name,
    department_name,
    city
FROM
    employees e,
    departments d,
    locations l
WHERE
    e.`department_id`=d.`department_id`
AND
    d.`location_id`=l.`location_id`
AND 
    city LIKE 's%';

The above is the detailed content of Analysis of connection query examples in MySQL. For more information, please follow other related articles on the PHP Chinese website!