Left JOIN, right connection, inner connection, outer join, join, left JOIN, Starboard join, MySQL, Oracle

2016-6-12 22:35:51

Working for more than a year of Oracle, recently learning MySQL, think carefully about the various connections, feel these concepts are quite annoying! Recently organized a bit, share their own understanding, some things are borrowed from the Internet and absorbed by themselves.

1. No matter what the connection is, the principle of Oracle and MySQL is exactly the same, but some of the wording is different. Speaking of writing, here's a little bit.

SELECT * from A, B where a.filed1=b.filed2; -This is the 1th kind of writing, the inner connection, so write, very convenient, Oracle and MySQL Universal

SELECT * from A join B on A.filed1=b.field2; -This is the 2nd kind of writing, the internal connection, so write, not very convenient, the veteran will not write this, Oracle and MySQL general,

Inside the connection, but outside the connection, Oracle has its own extra-specific wording, MySQL can only be used to the general wording ...

2. Several concepts

Left outer connection = left connection

Right connection = right outer connection

Fully connected, not to say, too simple. Internal connection, usually use the most, the simplest equals number connection example: There are two tables, assuming that A and B are many-to-one relationship a LEFT join B the number of records connected to a table with the number of records A Right JoinThe number of records connected to B is equal to the number of records connected in A and B plus the number of records on the B no match
This way, you can understand the left join and right connections. A left Join BEquivalent B Right Join A.Attach the test script to the most classic Oracle of Scott users under the departmental staff table as an example! Oralce Script--run in SQL window

CREATE TABLE EMP
(
Empno Number (4) is not NULL,
Ename VARCHAR2 (10),
Job VARCHAR2 (9),
Mgr Number (4),
HireDate DATE,
Sal Number (7,2),
Comm Number (7,2),
Deptno Number (2),
DESC2 VARCHAR2 (+) Default 2
)
;
--Create/recreate primary, unique and foreign KEY constraints
ALTER TABLE EMP
Add constraint Pk_emp primary key (EMPNO);
ALTER TABLE EMP
Add constraint Fk_deptno foreign key (DEPTNO)
References DEPT (DEPTNO);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7369, ' SMITH ', ' clerk ', 7902, to_date (' 17-12-1980 ', ' dd-mm-yyyy '), 800.00, 1999.00, and null);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7499, ' ALLEN ', ' salesman ', 7698, to_date (' 20-02-1981 ', ' dd-mm-yyyy '), 1600.00, 300.00, +, NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7521, ' WARD ', ' salesman ', 7698, to_date (' 22-02-1981 ', ' dd-mm-yyyy '), 1250.00, 500.00, +, NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7566, ' JONES ', ' MANAGER ', 7839, to_date (' 02-04-1981 ', ' dd-mm-yyyy '), 2975.00, NULL, and NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7654, ' MARTIN ', ' salesman ', 7698, to_date (' 28-09-1981 ', ' dd-mm-yyyy '), 1250.00, 1400.00, +, NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7698, ' BLAKE ', ' MANAGER ', 7839, to_date (' 01-05-1981 ', ' dd-mm-yyyy '), 2850.00, NULL, +, NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7782, ' CLARK ', ' MANAGER ', 7839, to_date (' 09-06-1981 ', ' dd-mm-yyyy '), 2450.00, NULL, ten, NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7788, ' SCOTT ', ' ANALYST ', 7566, to_date (' 19-04-1987 ', ' dd-mm-yyyy '), 3000.00, NULL, and NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7839, ' KING ', ' president ', NULL, to_date (' 17-11-1981 ', ' dd-mm-yyyy '), 5000.00, NULL, ten, NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7844, ' TURNER ', ' salesman ', 7698, to_date (' 08-09-1981 ', ' dd-mm-yyyy '), 1500.00, 0.00, +, NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7876, ' ADAMS ', ' clerk ', 7788, to_date (' 23-05-1987 ', ' dd-mm-yyyy '), 1100.00, NULL, and NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7900, ' JAMES ', ' clerk ', 7698, to_date (' 03-12-1981 ', ' dd-mm-yyyy '), 950.00, NULL, +, NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7902, ' FORD ', ' ANALYST ', 7566, to_date (' 03-12-1981 ', ' dd-mm-yyyy '), 3000.00, NULL, and NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7934, ' MILLER ', ' clerk ', 7782, to_date (' 23-01-1982 ', ' dd-mm-yyyy '), 1300.00, NULL, ten, NULL);

INSERT INTO EMP (EMPNO, ename, JOB, MGR, HireDate, SAL, COMM, DEPTNO, DESC2)
VALUES (7935, ' KATE ', ' MANAGER ', NULL, NULL, NULL, NULL, NULL, NULL);

Department table

--Create table
CREATE TABLE DEPT
(
Deptno Number (2) is not NULL,
Dname VARCHAR2 (14),
Loc VARCHAR2 (13)
)
;
--Create/recreate primary, unique and foreign KEY constraints
ALTER TABLE DEPT
Add constraint Pk_dept primary key (DEPTNO);

INSERT INTO Dept (DEPTNO, Dname, LOC)
VALUES (' ACCOUNTING ', ' NEW YORK ');

INSERT INTO Dept (DEPTNO, Dname, LOC)
VALUES ("DALLAS");

INSERT INTO Dept (DEPTNO, Dname, LOC)
VALUES (+, ' SALES ', ' CHICAGO ');

INSERT INTO Dept (DEPTNO, Dname, LOC)
VALUES (+, ' OPERATIONS ', ' BOSTON ');

The test statements are as follows:

SELECT * from emp e,dept D where E.deptno (+) =d.deptno;--oracle unique right join notation
SELECT * from emp e,dept D where E.deptno=d.deptno (+);--oracle Unique left join notation

SELECT * from emp e,dept d where E.deptno=d.deptno; --Inner connection, also known as natural connection
SELECT * FROM emp e full join dept D on E.deptno=d.deptno; --Fully connected, full join

Say a note about Oracle's external connections!

SELECT * from emp e,dept D where E.deptno (+) =d.deptno and e.empno (+) like ' 78% ';

Outside the connection, on the secondary side, no matter what the conditions of screening, remember to add (+), otherwise it will lead to data leakage, the actual found into the internal connection!!!

Here is the MySQL script and the test statement **************************************************************** *************************

EMP Table

CREATE TABLE EMP (
Empno varchar (12),
ename varchar (30),
Job varchar (27),
Mgr varchar (12),
HireDate Date,
Sal varchar (21),
Comm varchar (21),
Deptno varchar (6)
);
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7369 ', ' SMITH ', ' clerk ', ' 7902 ', ' 1980-12-17 ', ' + ', NULL, ' 20 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7499 ', ' ALLEN ', ' salesman ', ' 7698 ', ' 1981-02-20 ', ' 1600 ', ' 300 ', ' 30 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7521 ', ' WARD ', ' salesman ', ' 7698 ', ' 1981-02-22 ' , ' 1250 ', ' 500 ', ' 30 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7566 ', ' JONES ', ' MANAGER ', ' 7839 ', ' 1981-04-02 ') , ' 2975 ', NULL, ' 20 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7654 ', ' MARTIN ', ' salesman ', ' 7698 ', ' 1981-09-2 8 ', ' 1250 ', ' 1400 ', ' 30 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7698 ', ' BLAKE ', ' MANAGER ', ' 7839 ', ' 1981-05-01 ') , ' 2850 ', NULL, ' 30 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7782 ', ' CLARK ', ' MANAGER ', ' 7839 ', ' 1981-06-09 ') , ' 2450 ', NULL, ' 10 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7788 ', ' SCOTT ', ' ANALYST ', ' 7566 ', ' 1987-04-19 ') , ' n ', NULL, ' 20 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7839 ', ' KING ', ' president ', NULL, ' 1981-11-17 ', ' A ', NULL, ' 10 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7844 ', ' TURNER ', ' salesman ', ' 7698 ', ' 1981-09-0 8 ', ' 1500 ', ' 0 ', ' 30 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7876 ', ' ADAMS ', ' clerk ', ' 7788 ', ' 1987-05-23 ', ' 1100 ', NULL, ' 20 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7900 ', ' JAMES ', ' clerk ', ' 7698 ', ' 1981-12-03 ', ' 950 ', NULL, ' 30 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7902 ', ' FORD ', ' ANALYST ', ' 7566 ', ' 1981-12-03 ', ' n ', NULL, ' 20 ');
INSERT INTO EMP (empno, ename, Job, Mgr, HireDate, Sal, comm, Deptno) VALUES (' 7934 ', ' MILLER ', ' clerk ', ' 7782 ', ' 1982-01-23 ', ' 1300 ', NULL, ' 10 ');

CREATE TABLE Dept (
Deptno varchar (6),
Dname varchar (42),
Loc varchar (39)
);
INSERT INTO Dept (Deptno, dname, loc) VALUES (' n ', ' MARKETING ', ' Chongqing ');
INSERT INTO Dept (Deptno, dname, loc) VALUES (' Ten ', ' ACCOUNTING ', ' NEW YORK ');
INSERT INTO Dept (Deptno, dname, loc) VALUES (' + ', ' ['] ', ' DALLAS ');
INSERT INTO Dept (Deptno, dname, loc) VALUES (' + ', ' SALES ', ' CHICAGO2 ');
INSERT INTO Dept (Deptno, dname, loc) VALUES (' + ', ' OPERATIONS ', ' BOSTON2 ');

--I wrote the location table myself, in order to test the more complex 3 tables left connected, right connected!

CREATE TABLE location (
Locno varchar (30),
Locdesc varchar (90)
);
Insert into location (LOCNO, Locdesc) VALUES (' New York ', ' New York ');
Insert into location (LOCNO, Locdesc) VALUES (' DALLAS ', ' Dallas ');
Insert into location (LOCNO, Locdesc) VALUES (' CHICAGO ', ' Chicago ');
Insert into location (LOCNO, Locdesc) VALUES (' BOSTON ', ' Boston ');
Insert into location (LOCNO, Locdesc) VALUES (' Beijing ', ' Beijing ');
Insert into location (LOCNO, Locdesc) VALUES (' Shanghai ', ' Shanghai ');
Insert into location (LOCNO, Locdesc) VALUES (' XIAN ', ' Xian ');

Test statement:

Select
*
From
Dept D
Right join EMP E
On d.deptno = e. ' Deptno '
Left join location L
On D. ' loc ' = L. ' Locno '
ORDER by D. ' Deptno ';

Select
*
From
Dept D
Right join EMP E
On d.deptno = e. ' Deptno '
Right join location L
On D. ' loc ' = L. ' Locno '
ORDER by D. ' Deptno ';

Select
*
From
Dept D
Left join EMP E
On d.deptno = e. ' Deptno '
Right join location L
On D. ' loc ' = L. ' Locno '
ORDER by D. ' Deptno ';

Through the above three statements to test the discovery,

Continue with examples A and B:

a left joins B on A.field1=b.field2, the values of the Field1 columns all have, a right joins B on A.field1=b.field2, the values of the Field2 columns are all there, The number of connections is the number of inner joins + the number of extra rows in the primary connection   The left connection, in order to left join the column value set to the right!   multiple outer joins, connected in left-to-right order the last of these sentences is not very accurate, but the general meaning, understanding.

The above is my personal understanding, hope to help you, at the same time there are mistakes, please correct me!

Left JOIN, right connection, inner connection, outer join, join, left JOIN, Starboard join, MySQL, Oracle