Cartesian product problem and multi-table connection operation in SQL

(Scott account)

SELECT * from Scott.dept;--4
SELECT * from scott.emp;--14

/**
Cartesian product
Internal connection (equivalent connection)
External connections (non-equivalent connections)
Self-connect
*/

--Descartes Product
--when querying for data without using a join condition, all associated data is detected
--4*14=56
SELECT * from Scott.dept,scott.emp;

--Note: Multi-table connection query must be associated with the condition, otherwise there will be Cartesian product
--Summary: Multi-table Connection The first step is to find the association conditions
--emp.deptno = Dept.deptno
--student.clazz_id = Clazz.id
--N-1 when the association condition of a multi-table connection

--Inner connection (equivalent connection)
SELECT *
From Scott.dept,scott.emp
WHERE Scott.dept.deptno = Scott.emp.deptno
and scott.emp.empno = ' 7369 ';

SELECT *
From Scott.dept,scott.emp
WHERE Scott.dept.deptno = Scott.emp.deptno;

--Simplify development with aliases
SELECT *
From Scott.dept d,scott.emp E
WHERE D.deptno = E.deptno
and e.empno = ' 7369 ';

SELECT *
From Scott.dept d,scott.emp E
WHERE D.deptno = E.deptno;

--Filter the columns of the query
--Query e table all columns and D table dname column, D table LOC column
SELECT e.*, D.dname,d.loc
From Scott.emp e,scott.dept D
WHERE D.deptno = E.deptno;

--Internal connection (equivalent connection) standard SQL notation
SELECT *
From Scott.dept D INNER JOIN scott.emp E
on d.deptno = E.deptno;

--the same result from the WHERE statement
SELECT *
From Scott.dept,scott.emp
WHERE Scott.dept.deptno = Scott.emp.deptno;

--External connection (non-equivalent connection)
SELECT * from Scott.dept;--4
SELECT * from scott.emp;--14

--Query All department and employee information
SELECT *
From Scott.dept d,scott.emp E
WHERE D.deptno = E.deptno;

--Because department 40 does not have employees, when the equivalent connection is used, the information of department 40 is not displayed
--Left Outer connection: + number on the right, the left side of the data to show all, if the right side does not match the table data to the left, then fill the null
SELECT *
From Scott.dept d,scott.emp E
WHERE D.deptno = E.deptno (+);

--Right outer connection: + number on the left, the right side of the data to show all, if the left side does not match the right data, then fill null
SELECT *
From Scott.dept d,scott.emp E
WHERE E.deptno (+) = D.deptno;

--Note: The outer connection of the + sign is only supported by Oracle

--Standard SQL statements
--Left outer connection
SELECT *
From Scott.dept D left OUTER JOIN scott.emp E
on d.deptno = E.deptno;

--Right outer connection
SELECT *
From Scott.emp e right OUTER JOIN scott.dept D
on d.deptno = E.deptno;

SELECT *
From Scott.dept D right OUTER JOIN scott.emp E
on d.deptno = E.deptno;

--Full outer connection
--the table on the left and the table data on the right are all displayed, and if there is no corresponding null
SELECT *
From Scott.dept D full OUTER JOIN scott.emp E
on d.deptno = E.deptno;

SELECT *
From Scott.dept D left OUTER JOIN scott.emp E
on d.deptno = E.deptno;

-Self-linking (important, interview often appears)
/**
Each of the employees in the EMP has their own MGR (manager), and each manager himself is a company employee,
The manager himself also has his own manager. Below we need to find out each employee's name and manager's name.
What do we do at this time?
*/
SELECT * from Scott.emp;
--empno ename Mgr Mgrname
--7369 SMITH 7902 FORD
--empno ename Mgr Mgrname
--7902 FORD 7566 JONES

--Multi-table connection
--the fastest way to back up a table's data, note that the method simply backs up the data and does not copy the constraints
CREATE TABLE tb_emp as SELECT * from Scott.emp;
SELECT * from Tb_emp;
SELECT * from Scott.emp;

Employee name of employee number E of the--E employee name E of the manager number T
SELECT E.empno,e.ename,e.mgr,t.ename
From Scott.emp e,scott.tb_emp t
WHERE e.mgr = T.empno
and e.empno = ' 7369 ';

--Self-connected
SELECT E.empno,e.ename,e.mgr,t.ename
From Scott.emp e,scott.emp t
WHERE e.mgr = T.empno
and e.empno = ' 7369 ';

/**
A self-join is a frequently used connection in SQL statements.
Using a self-join allows one mirror of its own table to be treated as another table,
A table is considered as multiple tables to make connections, so that some special data can be obtained.
The key point is to specify two different aliases for the same table.
This makes it possible to find data that has an inheritance relationship by finding interrelated data in a single table.
*/

Cartesian product problems and multi-table join operations in SQL