Problems with join queries and subqueries in MySQL

Basic syntax for multi-table joins

Multi-table connection is to combine several tables into one table and then query it

select field1, field2, ...
from table1 {inner|lift|right} join table2
on connection condition;

There are two tables: department table and employee table

Cross join and Cartesian product phenomenon

Cross Connect

Cross join, also known as unconditional inner join/Cartesian join

Each item in the first table will be combined with each item in the other table in sequence.

select * from employee,department; 

The above result is definitely not what we want to know. Each person in the left table has 4 departments. A careful look at these 4 records is exactly the result of matching each record in the left table with the right table one by one.

Cartesian product phenomenon

The reason for the Cartesian product phenomenon: there is no valid connection condition between the two tables. Since you don't have a join condition, the first row in this table can definitely be matched one-to-one with all the rows in the other table. Similarly, the second row in this table can definitely be matched one-to-one with all the rows in the other table. And so on, the last row m in this table can also be matched one-to-one with all the rows in the other table. If the other table has n rows, then the number of rows displayed at the end must be m*n rows.

If you do not want to produce Cartesian product phenomenon, you need to add effective table join conditions. Taking the above example, the left table dep_id represents their department only when it is equal to the right table id.

Inner Join

INNER JOIN is to find the intersection of several tables, that is, to filter out the correct results based on the conditions.

select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp INNER JOIN department as dep
on emp.dep_id=dep.id; 

Since there is no department with id=5 in the department table, the record with dep_id=5 in the employee table is not returned; and since there are no employees行政部, this record is not returned either.

Outer Join

Left Outer Join

The left join is based on the left table. If there is no suitable record in the right table, it is filled with NULL . Its essence is to add records that have results in the left table but not in the right table on the basis of the inner join (in the case of inner join, the records in this case will be ignored).

select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp left join department as dep
on emp.dep_id=dep.id; 

Right Outer Join

The opposite of the left join, the right join is based on the right table. If some fields in the left table do not have suitable results, they are filled with NULL . Its essence is to add records that have results in the right table but not in the left table on the basis of the inner join (in the case of inner join, records in this case will be ignored).

select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp right join department as dep
on emp.dep_id=dep.id; 

Full outer join

Full outer join, based on inner join, displays all records of the left and right tables, and the default records in the left and right tables are filled with NULL .

There is no FULL JOIN syntax for full outer join in MySQL, but it is implemented with the help of UNION/UNION ALL statements.

The difference between UNION and UNION ALL is that UNION has the function of deduplication.

select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp left join department as dep
on emp.dep_id=dep.id
union
select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp right join department as dep
on emp.dep_id=dep.id; 

Subqueries

• A subquery is a query method that nests a query statement in another query statement:
• The inner query result of the subquery can be used as the outer query statement to provide query conditions.
• A subquery can contain keywords such as IN , NOT IN , AND , ALL , EXISTS , and NOT EXISTS .

Subqueries can also contain comparison operators such as = , != , > , < , etc.

-- Query the name of the department with an average age of more than 20 select name
from department
where id in (
select dep_id
from employee
group by dep_id
having avg(age) > 20);

-- Query the name of the employee in the Finance Department select name 
from employee
where dep_id in (
select id 
from department 
where name='Finance Department');


-- Query the age and name of all employees older than the average age select name,age 
from employee 
where age > (
select avg(age) from employee); 

This is the end of this article about the problems of join queries and subqueries in MySQL. For more relevant MySQL join queries and subqueries, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!