MySQL learning - connection query

join query

What connection query?

	In the actual development, in most cases, the data is not queried from a single table. Generally, multiple tables are queried jointly to get the final result.
	In actual development, a business usually corresponds to multiple tables. For example, students and classes have to save at least two tables. Of course, it can also be stored in a table, but if so much information is stored in a table, there will be a lot of duplication of data, resulting in data redundancy.

Classification of connection query?

Words divided according to the age of grammar, including:
SQL92 (some old DBAs may still use this syntax. DBA: DataBase, Administrator, DataBase Administrator)
SQL99 (relatively new syntax)

It is divided according to the connection mode of the table, including

**Inner connection**: 
	**Equivalent connection**
	**Non equivalent connection**
	**Self connection**
**External connection:**
	**Left outer connection (left connection)
	Right outer connection (right connection)**
**Full connection**(This is rarely used)

Connection query principle and Cartesian product phenomenon

Cartesian product phenomenon: when two tables are connected for query, there is no restriction. The number of final query results is the product of the number of records in the two tables.

What are the benefits of table aliases when querying linked tables?
A: first, the implementation efficiency is high; Second, good readability.

How to avoid Cartesian product Filter by adding conditions
Thinking: will the matching times of records be reduced by avoiding Cartesian product No, the matching times remain the same, but the valid records are displayed.

Equivalent connection of inner connection

The biggest feature is that the condition is equal quantity relationship.

mysql> select b.code,b.name,s.type,s.type_name
    -> from scan_problem_info s
    -> join
    -> base_community b
    -> on
    -> s.community_code = b.code;
+-------+-----------------------+------+--------------------+
| code  | name                  | type | type_name          |
+-------+-----------------------+------+--------------------+
| 35    | Zhidao apartment              | 1    | Lack of basic information       |
| 101   | Huhang Village                | 1    | Lack of basic information       |
| 01210 | East test area 9         | 1    | Lack of basic information       |
| 200   | Gateway free cell 200         | 1    | Lack of basic information       |
| 200   | Gateway free cell 200         | 6    | Abnormal device access       |
| 200   | Gateway free cell 200         | 6    | Abnormal device access       |
| 200   | Gateway free cell 200         | 6    | Abnormal device access       |
| 200   | Gateway free cell 200         | 6    | Abnormal device access       |
| 200   | Gateway free cell 200         | 6    | Abnormal device access       |
| 200   | Gateway free cell 200         | 6    | Abnormal device access       |
| 200   | Gateway free cell 200         | 6    | Abnormal device access       |
| 02    | Phoenix                | 5    | other               |
| 11    | Ruiyuan xiangxihai            | 6    | Abnormal device access       |
| 101   | Huhang Village                | 6    | Abnormal device access       |
| 01210 | East test area 9         | 6    | Abnormal device access       |
| 03040 | Jiaonan central kindergarten        | 6    | Abnormal device access       |
+-------+-----------------------+------+--------------------+
81 rows in set (0.00 sec)

Production and demand are based on the same equal volume relationship
Syntax:... A (inner) join B on connection condition where
The syntax structure of SQL99 is clearer, and the connection condition of the table is separated from the subsequent where condition.

Non equivalent connection of inner connection

Features: the relationship in the connection condition is unequal.
Case: find out the salary grade of each employee. It is required to display employee name, salary (sal) and salary grade?
select e.name,e.sal,s.grade from emp e join salgradde s on e.sal between s.losal and s.hisal

e.sal between s.losal and s.hisal is not an equivalent relationship, so it is a non equivalent connection.

Self connection of internal connection

Features: one table is regarded as two tables. Self and self
Case: find out the superior leader of each employee and display the employee name and corresponding leader name.

mysql > select 
	  > a.ename as 'Employee name',b.ename as 'Leader name'
	  > from 
	  > emp a
	  > inner join
	  > emp b
	  > on
	  > a.mgr = b,empno;

External connection

What is external connection and what is the difference between internal connection and external connection?

Internal connection: suppose that tables A and B are connected. If internal connection is used, all records on tables A and B can be queried. This is internal connection.

There is no distinction between the main table and the auxiliary table. The two tables are equal.

External connection: suppose tables A and B are connected. If external connection is used, one of the two tables AB is the primary table and the other is the secondary table. It mainly queries the data in the primary table and the secondary table. When the data in the secondary table does not match the data in the primary table, the secondary table automatically simulates NULL to match it.

Classification of external connections:
Left outer connection (left connection): indicates that the table on the left is the main table
Right outer connection (right connection): indicates that the table on the right is the main table

The left connection has the writing method of the right connection, and the right connection will also have the corresponding writing method of the left connection.

Case: find out the superior leaders of each employee (all employees must be found out)
Left outer connection:

mysql > select 
	  > a.ename as 'Employee name',b.ename as 'Leader name'
	  > from 
	  > emp a
	  > left (outer) join      //outer can be omitted
	  > emp b
	  > on
	  > a.mgr = b,empno;


Right outer connection

mysql > select 
	  > a.ename as 'Employee name',b.ename as 'Leader name'
	  > from 
	  > emp b
	  > right join
	  > emp a
	  > on
	  > a.mgr = b,empno;

The result is the same as the picture above

Case: find out which department has no employees?

mysql > select e.*,d.*
	  > from
	  > emp e
	  > right join
	  > dept d
	  > on
	  > e.deptno = d.deptno;


This has not been found yet. You need to filter a condition.

mysql > select e.*,d.*
	  > from
	  > emp e
	  > right join
	  > dept d
	  > on
	  > e.deptno = d.deptno
	  > where
	  > e.empno is null;

Full connection: all associated tables belong to the primary table (understand)

Connection query of 3 tables

Case: find out the Department name and salary grade of each employee
be careful:

	............
		A
	join
		B
	join
		C
	on
		............
express: A Table and B Tables are connected first, and then connected A Table continue and C Table.

So the sql statement of this case

mysql > select e.*,d.*
	  > from
	  > emp e
	  > join
	  > dept d
	  > on
	  > e.deptno = d.deptno
	  > join
	  > salgrade s
	  > on
	  > e.sal between s.losal and s.hisal;


Case: find out the Department name, salary name and superior leaders of each employee.

mysql > select e.ename 'staff',d.dname,s.grade,el.eanme 'leader'
	  > from emp e
	  > join dept d
	  > on e.deptno = d.deptno
	  > join salgrade s
	  > on e.sal between s,losal and s.hisal
	  > left join emp el
	  > on e.mgr = el,empno;

Tags: Database MySQL DBA

Posted on Mon, 08 Nov 2021 00:39:19 -0500 by jengle52