The data tables involved in this article can be consulted independently from the previous "MySQL Difficult Grammar - Subquery"

There are three ways to connect MySQL data tables: equivalent connection, external connection and self-connection. Here are three ways to connect

1. Equivalent connection

Equivalent joins generally apply to joins between primary and external tables (primary keys of primary tables are equal to external keys of primary tables)

Requirements: 1. Check the name of the Department in which all employees work

select s_emp.id staff,last_name Employee Name,s_dept.id Department Number, name Department name
from s_dept, s_emp
where s_dept.id = s_emp.dept_id;

Needs: 2. Query departments whose average wage is more than 1200 and display their names.

select s_dept.id, name
from s_emp, s_dept
where s_emp.dept_id=s_dept.id
group by dept_id
    having avg(salary) > 1200;

Requirements: 3. View the name of the area where all employees are located

select last_name, s_dept.id, s_region.name
from s_emp, s_dept, s_region
where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id

Requirements: 4. Look at the employee's id, last_name, salary, department name and area name. These employees have the following conditions: salary is higher than the average salary in Chang's area or they are not in the same department as Chang's employees.

select s_emp.id, last_name, salary, s_dept.name, s_region.name
from s_emp, s_dept, s_region
where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id
and (salary > (         # Average Wages of Departments in chang's Region
    select avg(salary) 
    from s_emp
    where dept_id in (        # Departments in chang's region
        select id
        from s_dept
        where region_id=(         # Chang's area
            select region_id
            from s_emp, s_dept
            where s_emp.dept_id=s_dept.id and last_name="chang"
        )
    ) or last_name != "chang")
);

Requirements: 5. Look at the id and name of the employee whose salary is higher than the average salary of the department.

select s_emp.id, last_name
from s_emp, s_dept, ( # Average salary scale for each department
    select dept_id, avg(salary) as avg_salary 
    from s_emp 
    group by dept_id) as avg_table
where s_emp.dept_id=s_dept.id
    and s_dept.id=avg_table.dept_id     # Linking Departmental Number to Departmental Salary Scale
    and salary > avg_salary;

Requirement: 6. Query the employees whose salary is higher than the average salary of department 41, and the average salary of the Department where the employee belongs is also higher than the average salary of department 41, showing the average salary of the Department where the employee belongs and the name of the Department where the employee belongs.

select e.dept_id, n.avg_salary, d.name
from s_emp as e, s_dept as d, (   # Departmental Salary Scale
            select dept_id, 
                avg(salary) as avg_salary 
            from s_emp group by dept_id) as n
where e.dept_id=d.id and d.id=n.dept_id 
and n.dept_id != 41   # No. 41 Department exists if it is larger than
and salary > (      # Average salary greater than department 41
    select avg_salary 
    from (     # Departmental Salary Scale
        select dept_id, 
            avg(salary) as avg_salary 
        from s_emp group by dept_id) as n 
    where dept_id =41)

2. External connection

External connection can be divided into two kinds: left connection and right connection.

Left join: Table 1 left join table 2... on table and table association, followed by a condition, not necessarily two tables equivalent relationship (Table 1 data is complete)

Requirements: View customer names and their corresponding salesperson names

select c.name, e.last_name
from s_customer as c left join s_emp as e    # left join
on e.id=c.sales_rep_id;

Right join: Table 1 right jion table 2.... On table and table association, on followed by a condition, not necessarily two tables equivalent relationship (Table 2 data is complete)

Requirements: View customer names and their corresponding salesperson names

select c.name, e.last_name
from s_emp as e right join s_customer as c    # Right Connection
on e.id=c.sales_rep_id;

3. Self-connection

As its name implies, it is its own table association. There is a foreign key associated with its own primary key in the table. It has its own leader in the employee table, but the leader itself is an employee: An example is as follows.

Requirements: Check the names of all employees and their corresponding managers

select id, last_name
from s_emp
where manager_id = id