join query

Meaning: it is also called multi table query. When the query fields come from multiple tables, the join query will be used

Cartesian product phenomenon: Table 1 has m Row, table 2 has n Line, result=m*n that 's ok
 Cause: there is no valid connection condition
 How to avoid: adding valid connection conditions
 As follows:
#Without conditions
select name,boyName from boys,beauty; # At this time, the table appears with m*n rows. Use the data in the boys table to match the data in the beauty table one by one
#After adding conditions
select name,boyName from boys,beauty where beauty.boyfriend_id = boys.id;

Classification of connection query:
	Classification by age:
		sql92 Standard: only internal connections are supported
		sql99 Standard [recommended]: internal connection is supported + Outer connection (left outer and right outer) + Cross connect
	Classification by function:
		Inner connection
			Equivalent connection
			Non equivalent connection
			Self connection
		External connection
			Left outer connection
			Right outer connection
			Total external connection
		Cross connect

1, sql 92 standard

1,The result of multi table equivalent connection is the intersection of multi tables
2,n Table connection requires at least n-1 Conditions
3,The order of multi table connection is not required
4,Aliasing tables is generally required
5,It can be combined with all the clauses described above, such as sorting, filtering, grouping, etc

1. Equivalent connection

Case 1: query the male name corresponding to the female name and the male name

select 
	name,boyName 
from 
	boys,beauty 
where 
	beauty.boyfriend_id = boys.id;

Case 2: query employee name and corresponding department name

select
	last_name,department_name
from
	employees,departments
where
	employees.department_id = departments.department_id;

Alias table

effect:
	1,Improve the conciseness of sentences
	2,Distinguish ambiguous fields

Alias is in from After, add after the table name, as alias (as Can be omitted)
[Note: if a table is aliased, the query field cannot be qualified with the original table name(Generate virtual view). 

Query employee name, type of work number and type of work name

select 
	e.last_name,e.job_id,j.job_title
from
	employees e,jobs j
where
	e.job_id = j.job_id;

Add filter criteria

Case 1: query employee name and department name with bonus

select
	e.last_name,d.department_name,commission_pct
from
	employees e,departments d
where
	e.department_id = d.department_id and
	commission_pct is not null;
# Use and connection between conditions

Case 2: query the Department name and city name whose second character is o in the city name

select
	d.department_name,l.city
from
	departments d,locations l
where
	d.location_id = l.location_id and
	city like '_o%';

Add group

Case 1: query the number of departments in each city

select 
	count(*) number,city
from
	departments d,locations l
where
	d.location_id = l.location_id
group by
	city;

Case 2: query the Department name, department leader number and minimum wage of each department with bonus

select 
	d.department_name,d.manager_id,min(salary)
from
	departments d,employees e
where
	d.department_id = e.department_id and
	commission_pct is not null;
group by
	department_name,d.manager_id;

Add sort

Query the name of each type of work and the number of employees, in descending order by the number of employees

select 
	job_title,count(*)
from
	jobs j,employees e
where
	e.job_id = j.job_id
group by
	job_title
order by count(*) desc;

Realize three table connection

Case: query employee name, department name and city

select
	last_name,department_name,city
from
	departments d,employees e,locations l
where
	d.department_id = e.department_id and
	d.location_id = l.location_id;
#You can add grouping, sorting, and other conditions on the basis of the following

2. Non equivalent connection

Case 1: query employee's salary and salary level

select 
	salary,grade_level
from	
	employees e,job_grades g
where
	salary between lowest_sal and highest_sal;
	and g.grade_level='A';
# Non equivalent connection can add other conditions, sorting and grouping, etc

3. Self connection

Case: query employee name and superior name

#Use the original table as two or more tables. One field of the table corresponds to another field of that table
select
	e1.last_name,e2.last_name
from
	employees e1,employees e2
where
	e1.manager_id = e2.employee_id;

Self connection test

1, Displays the maximum salary and average salary of the employee table
select max(salary), avg(salary) from  employees;

2, Query employee table employee_id , job_id , last_name , Press department_id In descending order, salary Ascending order
select 
	employee_id,job_id,last_name
from
	employees
order by
	department_id desc,salary asc;

3, Query employee table job_id Contains a and e Yes, and a stay e In front of
seelct job_id from employees where job_id like '%a%e%';

4, Known table student,There are id(Student number),name,gradeId(Grade number)
	Known table grade,There are id(Grade number),name(Grade name)
	Known table result,There are id,score,studentNo(Student number)
Name, grade and grade are required
select
	s.name,g.name,r.soorce
from
	student s,grade g,result r
where
	s.gradeId = s.id and s.id = r.studentNo;

5, The function that displays the current date, removes the space before and after, and intercepts the substring
select now();
select trim()
select substr(str,startIndex,lenth);

1. Displays the names, Department numbers and department names of all employees.
select 
	last_name,department_id,department_name
from
	departments d,employees e
where
	d.department_id = e.department_id;

2. Query the employee of department 90 job_id And department 90 location_id
select
	job_id,location_id
from
	departments d,employees e
where
	d.department_id=e.department_id
	and e.department_id=90;
	

3. Select all employees with bonus
e.last_name , d.department_name , l.location_id , l.city
select 
	last_name,depart_name,location_id,city
from
	employees e,departments d,locations l
where
	e.department_id = d.department_id
	and d.location_id = l.location_id
	and e.commission_pct is not null


4. choice city stay Toronto Of employees working
e.last_name , e.job_id , d.department_id , d.department_name 
select 
	last_name , job_id , department_id , department_name 
from	
	employees e,departments d,locations l
where
	e.department_id = d.department_id
	and d.location_id = l.location_id
	and city='Toronto'

5.Query the Department name, type of work name and minimum wage of each type of work and department
select
	department_name,job_title,min(salary)
from
	employees e,jobs j,departments d
where
	e.department_id = d.department_id
	j.job_id = e.job_id
group by
	job_title,department_name;
	
6.Query the country number with more than 2 departments in each country
select
	country_id,department_id,count(*) Number of departments
from
	departments d,locations l
where
	d.location_id = l.location_id
group by
	country_id
having
	count(*)>2;


7,Select the name and employee number of the specified employee, as well as the name and employee number of his manager. The result is similar to the box below
 type
employees 			Emp# 			manager Mgr#
kochhar 101 		king 			100

select 
	e.last_name,e.employee_id "Emp",m.last_name,m.employee_id "Mgr#" #Aliases have special characters. Double quotation marks are recommended
from
	employees e,employees m
where
	m.employee_id = e.manager_id
	end e.last_name='kochhar';

2, sql 99 standard

Classification [connection type]
	Inner connection	inner
	External connection	
		Left outer	left [outer]
		Right outer	right [outer]
		Total external full [outer]
	Cross connect cross join

Syntax:
	select Query list
	from	Table name alias [connection type]
	join Table 2 aliases on Connection conditions
	[where	Conditions]
	[group by grouping having Conditions]
	[order by Sort]
	

1, Inner connection

Syntax:
	select Query list
	from Table 1 aliases
	inner join Table 2 aliases
	on Connection conditions

Classification:
	Equivalent connection·
	Non equivalent connection
	Self connection

characteristic:
	1,Add sorting, grouping, filtering
	2,inner Can be omitted
	3,Filter criteria on where Later, the connection conditions are placed in on Later, it improves the separation and is easy to read.
	4,inner join Equivalent connection and sql 92 The effect of equivalent connection in is the same. They are all the intersection parts of query multiple tables
	

Equivalent connection

Query employee name and department name

select last_name,department_name
from	employees e
inner join departments d
on e.department_id = d.department_id;
#The effect is the same as that of sql 92 syntax

Employee name and type of work included e in query name (filter added)

select last_name,job_title
from employees e
inner join jobs j
on e.job_id = j.job_id #The type and length of associated columns should be the same as possible
where e.last_name like '%e%';

Query the city name and department number with department number > 3 (add grouping and filtering)

select count(*) Number of departments,city
from departments d
inner join locations l
on d.location_id = l.location_id
group by city
having count(*)>3;

Query the Department name and number of employees in which department the number of employees > 3, and sort them in descending order

select department_name,count(*) Number of employees
from employees e
inner join departments d
on e.department_id = d.department_id
group by department_name
having count(*)>3
order by count(*) desc;

Query employee name, department name, type of work, and in descending order by department name

select last_name,department_name,job_title
from employees e
inner join departments d on e.department_id = d.department_id
inner join jobs j on j.job_id = e.job_id
order by department_name desc;

/*
 When multiple tables are connected
	select Query list
	from Table 1 aliases
	inner join Table 2 alias on connection conditions
	inner join Table 3 alias on connection conditions
	...

Note: when connecting multiple tables, ensure that the following tables are connected with the previous tables

*/

2. Non equivalent connection

Query employee's salary level

select salary,grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;

Query the number of each salary level > 20, in descending order by salary level

select grade_level,count(*) Number of salary levels
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
group by grade_level
having count(*)>20
order by salary desc;

3. Self connection

Query employee's name and superior's name

select e.last_name,m.last_name
from employees e
join employees m
on e.manager_id = m.employee_id;

#You can filter, group and sort on this basis

2, External connection

Left (right) outer connection

Application scenario:
	External join is used to query records in one table that are not in another table

characteristic
1,Use table 1 to match table 2. At this time, table 1 is the master table and table 2 is the slave table
 When querying, all records in the main table will be displayed. If there is a matching value in the secondary table, it will be displayed. If there is no matching, it will be used null To fill (equivalent to dividing the table into two parts. The first part is equivalent to the result of inner connection, and the second part is used from the records not in the table null Filling, external connection=Internal connection result+Records in the master table but not in the slave table)
2,Left outer connection: left join On the left is the main table
	Right outer connection: right join On the right is the main table
3,The same effect can be achieved by exchanging the order of the two tables outside the left and right

Query the girl name whose boyfriend is not in the boy list

# Left outer connection
select b.name,bo.*
from beauty b
left outer join boys bo
on b.boyfriend_id = bo.id
where bo.id is null;

#Right outer connection
select b.name,bo.*
from boys bo
right outer join beauty b
on b.boyfriend_id = bo.id
where bo.id is null;

#When the main table is not replaced, the effect of the left external connection is the same as that of the right external connection

#Make boy the main table
select b.name,bo.*
from beauty b
right outer join boys bo
on b.boyfriend_id = bo.id
where bo.id is null;

Query which department has no employees

#Left outer connection
select d.*, e.employee_id
from departments d
left outer join employees e
on d.department_id = e.department_id
where e.employee_id is null;

#Right outer connection
select d.*, e.employee_id
from employees e
right outer join departments d
on d.department_id = e.department_id
where e.employee_id is null;

Total external connection

All the data in the two tables are displayed, and no records are used null Filling, regardless of master-slave table
(Total external connection = Internal connection result+Data in Table 1 but not in Table 2+(data in Table 2 but not in Table 1)

select b.*,bo.*
from beauty b
full outer join boys bo
on b.boyfriend_id = bo.id;

# All external connections are not supported in mysql

3, Cross connect

select b.*,bo.*
from beauty b
cross join boys bo;

Cross connect is equivalent to Cartesian product

Differences between sql 92 and sql 99

Function: sql 99 It supports many functions
 Readability: sql 99 It realizes the separation of connection conditions and filtering conditions, and has high readability

Query the intersection of two tables: inner join
 Query all records in Table 1 (Table 2): Use Table 1 (Table 2) as the main table for left (right) external connection
 Query the data in Table 1, remove the intersection part with table 2, and filter the primary key of Table 2 based on the left (right) external connection null

sql 92 syntax test

1, Query the boyfriend information of goddess with number > 3. If yes, list the details. If not, fill in null

select b.id,b.name,bo.*
from beauty b
left outer join boys bo
on b.boyfriend_id = bo.id
where b.id>3;

2, Query which city has no department

select city
from locations l
left join departments d
on d.location_id = l.location_id
where department_id is null;

3, Query the employee information of the department named SAL or IT

select d.department_name,e.*
from departments d
left join employees e
on e.department_id = d.department_id
where department_name in ('SAL','IT');