multi-table query

Table used in the example:
emp table

dept table

Syntax:

select * frome Table 1,Table 2, where condition;

Tip: the condition of multi table query cannot be less than - 1 of the number of tables, otherwise the Cartesian product will appear, that is, the result is the number of rows in Table 1 * the number of rows in Table 2.

Example 1:
Displays the employee name, employee salary and the name of the Department

#Query ename, Sal and dName from emp table and dept table. Deptno in emp should correspond to deptno in dept
select ename,sal,dname from emp,dept where emp.deptno = dept.deptno;

Example 2:
Query the Department name, employee name and salary with department No. 10

select ename,sal,dname,emp.deptno from emp,dept where emp.deptno = dept.deptno and emp.deptno=10;

Self connection

example:
Displays the employee name and the corresponding Supervisor Name

Analysis: employee name and supervisor name are in the same table. You can regard one table as two tables and query by multi table query

#For query, you need to give each table an alias, otherwise the system does not know which table is selected
#Treat the two tables as an independent table and fill in the condition information according to the normal idea

mysql> select e1.ename as 'staff',e2.ename as 'boss' from emp e1,emp e2 where e1.mgr = e2.empno;

Subquery

Subquery refers to embedding select statements in other sql statements, also known as nested query

single-row subqueries

Single row subquery refers to a subquery statement that returns only one row of data

example:
Displays all employees in the same department as SMITH

#Idea:
#    1. First query the department number of SMITH, select deptno from emp where ename = 'SMITH'
#       The above query statement only returns one query statement, so it is a single line sub query
#    2. Then use the number queried above as a condition to query all employees in the same department, as follows:
select * from emp where deptno = (select deptno from emp where ename = 'SMITH');

Multiline subquery

Multi row sub query refers to a sub query that returns multi row data, using the keyword in

example:
Query the (name, position, salary, department number) of employees who work the same as department 10

#Idea:
#    1. First, query the work of 10 departments. select distinct job from emp where deptno = 10;
#    2. When querying name, position, salary and department number, the filter criteria are
	select ename,job,sal,deptno from emp 
	where job in (select distinct job from emp where deptno=10);

#	3. Excluding employees of department 10
	select ename,job,sal,deptno from emp 
	where job in (select distinct job from emp where deptno=10)
	and deptno != 10;

all operator

example:
Query the name, salary and department number of employees whose salary is higher than that of all employees in department 30

#Idea:
#1. First query all the wages of department 30
SELECT sal FROM emp WHERE deptno = 30;

#2. Use the all operator as the filter condition, which means Sal > all (SELECT sal FROM emp WHERE deptno = 30)
SELECT ename,sal from emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);

any operator

example:
Query the name, salary and department number of the employee whose salary is higher than that of one of the employees in department 30

#Idea:
#1. First query all the wages of department 30
SELECT sal FROM emp WHERE deptno = 30;

#2. Use the any operator as the filter condition, which means Sal > any of them (SELECT sal FROM emp WHERE deptno = 30)
SELECT ename,sal from emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30);

Multiple-column subqueries

example:
Query all employees whose departments and positions are exactly the same as allen's (and allen himself is not included)

#Idea:
#1. First query allen's position and department
SELECT job,deptno FROM emp WHERE ename='ALLEN';

#2. Then use the above query statements in filtering
SELECT ename,job,deptno FROM emp
WHERE (job,deptno) = (SELECT job,deptno FROM emp WHERE ename='ALLEN') AND ename != 'ALLEN';

External connection

Question: multi table query is filtered through where to avoid Cartesian product. According to the matching conditions of where, all matching records are displayed, and those that do not match are not displayed. If you need to display those that do not match, you need to use external connection

SELECT *
FROM dept,(SELECT deptno,COUNT(*) AS `total` FROM emp GROUP BY deptno) emp
WHERE emp.deptno = dept.deptno;

Note: there are 4 departments (10, 20, 30, 40) in the dept table. The number of people in each department is counted. Department 40 has no personnel, so it will not be displayed if it does not match

Left outer connection (if the table on the left is completely displayed, it is the left outer connection)

Effect after using left outer connection

Right outer connection (if the table on the right is completely displayed, it is the right outer connection)

Display all information of employees in emp table (including department name and address in dept table), and list those departments without employees

SELECT *
FROM emp RIGHT JOIN dept
ON emp.deptno = dept.deptno;