Table Join Query

1. What is a multi-table query

• Preparing data

# Create Department Table
create table dept(
  id int primary key auto_increment,
  name varchar(20)
)
insert into dept (name) values ('Development Department'),('Marketing Department'),('Finance Department');
# Create employee table
create table emp (
  id int primary key auto_increment,
  name varchar(10),
  gender char(1), -- Gender
  salary double, -- wages
  join_date date, -- Date of entry
  dept_id int,
  foreign key (dept_id) references dept(id) -- Foreign key, associated department table(Primary key for department table)
)
insert into emp(name,gender,salary,join_date,dept_id) values('Sun WuKong','male',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('Zhu Bajie','male',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('Tang Monk','male',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('Osteoprotein','female',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('spider goblin','female',4500,'2011-03-14',1);

• The role of multitable queries:
  • For example, if we want to query the name of Sun Wukong and the name of his department, we need to use multi-table query.
  • If an SQL statement queries multiple tables, because the query results are in multiple different tables.Each table has one or more columns.

Classification of multitable queries:

2. Cartesian product phenomenon

2.1 What is the Cartesian product phenomenon

-- Requirements: Query all employees and all departments
select * from emp,dept;

2.2 How to Clear the Effect of Cartesian Product Phenomena

We found that not all data combinations were useful, only the employee table.dept_Data with ID =department table.id is useful.So conditional filtering is needed to filter out unused data.

-- Set filter conditions Column 'id' in where clause is ambiguous
select * from emp,dept where id=5;
select * from emp,dept where emp.`dept_id` = dept.`id`;
-- Query the names of employees and departments
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

3. Internal connection

Matches the records of the right table with the records of the left table and displays them if they meet the criteria.For example: Slave table. Foreign key = Primary table. Primary key

3.1 Privacy Connection

JOIN keyword not visible, condition specified using WHERE

-- SELECT Field name FROM Left Table, Right Table WHERE condition
select * from emp,dept where emp.`dept_id` = dept.`id`;

3.2 Display inner connection

You can omit INNER by using the INNER JOIN... ON statement

-- SELECT field name FROM left table [INNER] JOIN right table ON condition

Looking up the information of Tang Seng, showing the employee id, name, gender, salary and department name, we found that we need to combine two tables to query the data we need at the same time, using internal connection.

• 1) Which tables are actually queried

select * from emp inner join dept;

• 2) Determine table join conditions, employee table.dept_id = Department table. id data is valid

select * from emp e inner join dept d on e.`dept_id` = d.`id`;

• 3) Determine the query conditions, we are querying the information of Tang Monk, Employee Table.name='Tang Monk'

select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='Tang Monk';

• 4) Determine the query field, query the information of Tang monk, display employee id, name, gender, salary and department name

select e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='Tang Monk';

• 5) We found that writing table names was a bit long and could alias the table and display field names using aliases

select e.`id` number,e.`name` Full name,e.`gender` Gender,e.`salary` wages,d.`name` Department name from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='Tang Monk';

3.3 Summary inner join query steps:

• 1) Determine which tables to query
• 2) Determine conditions for table joins
• 3) Determine the conditions for the query
• 4) Determine the field of the query

4. Left outer connection

With LEFT OUTER JOIN... ON, OUTER can be omitted

-- SELECT field name FROM left table LEFT [OUTER] JOIN right table ON condition

Match the records of the right table with the records of the left table and display them if they meet the criteria; otherwise, display NULL

It can be understood that: on the basis of internal connection, the data of the left table is displayed completely (the left table is the department, the right table employees)

-- Add a sales department to the Department table
insert into dept (name) values ('Sales Department');
select * from dept;
-- Use inline join query
select * from dept d inner join emp e on d.`id` = e.`dept_id`;
-- Use left outer join query
select * from dept d left join emp e on d.`id` = e.`dept_id`;

5. Right Outer Connection

With RIGHT OUTER JOIN... ON, OUTER can be omitted

-- SELECT field name FROM left table RIGHT [OUTER] JOIN right table ON condition

Match the records of the left table with the records of the right table and display them if they meet the criteria; otherwise, display NULL

It can be understood as: on the basis of internal joins, all data in the right table is displayed

-- Add an employee to the employee list
insert into emp values (null, 'Sand Monk','male',6666,'2013-12-05',null);
select * from emp;

-- Use inline join query
select * from dept inner join emp on dept.`id` = emp.`dept_id`;
-- Use right outer join query
select * from dept right join emp on dept.`id` = emp.`dept_id`;

OK, that's it