MySQL database table between internal and external links detailed

1. Inner connection (natural connection)

2. External connection

(1) Left outer connection (the table on the left is unrestricted)
(2) Right outer connection (no restriction on the right table)
(3) Full-outer connection (no restriction on both the left and right tables)

3. Self-connect (connection within the same table)

Standard syntax for SQL:
Select Table1.column,table2.column from table1 [Inner | left | right | full] join table2 ontable1.column1 = Table2.column 2;

Inner JOIN indicates an inner connection;

The left join indicates the outer connection;

The right join represents the left outer join;

Full join represents a complete outer join;

The ON clause is used to specify the join condition.

Attention:

If you specify an inner or outer join using the FROM clause, you must specify the join condition using the ON clause;
If you specify an outer join using the (+) operator, you must specify the join condition by using the WHERE clause.

• Inner Connection ( Innerjoin/join )

1.1 Inner Join

The Inner join logical operator returns each row that satisfies the first (top) input and the second (bottom) input join. This is the same effect as querying multiple tables with SELECT, so there are few internal connections.

Another point to note is that join is inner join by default. So we can omit the Inner keyword when we write the inner connection.

1.2 For example, the following is a clear connection:

1.2.1 First create 2 test tables and insert the data:

Sql> select * from Dave;

ID NAME

---------- ----------

1 Dave

2 BL

1 bl

2 Dave

Sql> select * from BL;

ID NAME

---------- ----------

1 Dave

2 BL

1.2.3 Use the internal link to query:

Sql> Select a.id,a.name,b.name from Davea inner joins BL B on a.id=b.id; --Standard notation

ID name Name

---------- ---------- ----------

1 Dave Dave

2 BL bl

1 BL Dave

2 Dave Bl

Sql> Select a.id,a.name,b.name from Davea joins BL B on a.id=b.id; --The Inner keyword is omitted here

ID name Name

---------- ---------- ----------

1 Dave Dave

2 BL bl

1 BL Dave

2 Dave Bl

Sql> Select a.id,a.name,b.name from DAVEA,BL b where a.id=b.id; --Select multiple table query

ID name Name

---------- ---------- ----------

1 Dave Dave

2 BL bl

1 BL Dave

2 Dave Bl

From the results of these three SQL we can also see that their role is the same.

1.3 Natural Connection (Natural join)

A natural connection is to look for fields in two tables that have the same data type and column names, and then automatically connect them together and return all results that match the criteria.

Let's take a look at the examples of natural connections:

Sql> Select Id,name from Dave a naturaljoin bl B;

ID NAME

---------- ----------

1 Dave

2 BL

Here we do not specify the conditions for the connection, in fact Oracle has made a move for us, and the ID and name fields in the Dave table are connected to the ID and name fields in the BL table. Which is actually equivalent to

Sql> Select dave.id,bl.name from davejoin bl on dave.id = Bl.id and dave.name=bl.name;

ID NAME

---------- ----------

1 Dave

2 BL

Therefore, we can also understand the natural connection as one of the inner joins.

Some things to note about natural connections:

(1). If there are more than one field of the two tables of the natural connection that satisfy the same name type, then they will be treated as a natural connection condition.

(2). If the two tables that are naturally connected are only the same as the field names, but the data types are different, an error is returned.

• External Connection (Outerjoin)

Outer join returns a row for each join that satisfies the first (top) input and the second (bottom) input. It also returns any row in the first input that does not have a matching row in the second input. The outer connection is divided into three kinds: Left outer connection, right outer connection, full outer connection. Corresponds to the Sql:left/right/fullouter JOIN. Usually we omit the outer keyword. Written as: Left/right/full JOIN.

A table is the base table for both the left and right outer joins, and the contents of the table are all displayed, followed by two tables that match the contents. If the data in the base table is not recorded in another table. Then the columns in the associated result set row are displayed as null values (NULL).

For outer joins, you can also use "(+)" to represent them. Some things to note about using (+):
1. The (+) operator can only appear in the WHERE clause and cannot be used in conjunction with the outer join syntax.
2. When an outer join is performed using the (+) operator, if more than one condition is included in the WHERE clause, the (+) operator must be included in all conditions
3. The (+) operator applies only to columns, not to expressions.
4. The (+) operator cannot be used with the OR and in operators.
5. The (+) operator can only be used to implement left outer and right outer joins, not for full outer joins.
Before we do the experiment, we'll add some different data to the Dave table and BL. for easy testing.

Sql> select * from BL;

ID NAME

---------- ----------

1 Dave

2 BL

3 Big Bird

4 EXC

9 Huaining

Sql> select * from Dave;

ID NAME

---------- ----------

8 Anqing

1 Dave

2 BL

1 bl

2 Dave

3 DBA

4 sf-express

5 DMM

2.1 Left Outer connection (outer join/left join)

The left join is based on the left table record, and in the example Dave can be seen as the right-hand table, and BL can be seen as an rvalue, whose result set is the data in the Dave table, plus the data that matches the Dave table and the BL table. In other words, the records of the left table (Dave) are all represented, and the right table (BL) displays only those records that match the search criteria. Where the BL table is not logged is null.

Example:

Sql> Select *from Dave a LEFT join BL b on a.id = b.ID;

ID Name ID Name

--------- ---------- ---------- ----------

1 BL 1 Dave

1 Dave 1 Dave

2 Dave 2 bl

2 BL 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

5 DMM--the B table here is NULL because there is no match to

8 Anqing--Here the B table is null because there is no match to the

Sql> Select *from Dave a left outer joins BL B on a.id = b.ID;

ID Name ID Name

---------- ---------- ---------- ----------

1 BL 1 Dave

1 Dave 1 Dave

2 Dave 2 bl

2 BL 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

5 DMM

8 Anqing

With (+) to achieve, the + number can be understood as follows: + to supplement, that is, which table has a plus, this table is a matching table. So the plus sign is written in the right table, the left table is all displayed, so it is left connected.

Sql> Select *from Dave A,bl b where a.id=b.id (+); -NOTE: Use the keyword WHERE (+)

ID Name ID Name

---------- ---------- ---------- ----------

1 BL 1 Dave

1 Dave 1 Dave

2 Dave 2 bl

2 BL 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

5 DMM

8 Anqing

2.2 Right outer connection (Rightouter join/right join)

The result is the opposite of the left join, which is based on the right table (BL), which shows the BL table's record, plus the results of Dave and BL matching. The place where the Dave table is insufficient is filled with null.

Example:

Sql> SELECT * FROM Dave a right join bl bon a.id = b.id;

ID Name ID Name

---------- ---------- ---------- ----------

1 Dave 1 Dave

2 BL 2 bl

1 BL 1 Dave

2 Dave 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

9 Huaining--The left table here is insufficient to fill with null

7 rows have been selected.

Sql> SELECT * from Dave a right outerjoin bl b on a.id = b.ID;

ID Name ID Name

---------- ---------- ---------- ----------

1 Dave 1 Dave

2 BL 2 bl

1 BL 1 Dave

2 Dave 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

9 Huaining--The left table here is insufficient to fill with null

7 rows have been selected.

With (+) to achieve, the + number can be understood as follows: + to supplement, that is, which table has a plus, this table is a matching table. So the plus sign is written in the left table, the right table is all displayed, so the right connection.

Sql> Select * from Dave A,bl b wherea.id (+) =b.id;

ID Name ID Name

---------- ---------- ---------- ----------

1 Dave 1 Dave

2 BL 2 bl

1 BL 1 Dave

2 Dave 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

9 Huaining

2.3 All-out connection (full outer join/full join)

Neither the left table nor the right table is restricted, and all the records are displayed, where two tables are insufficient to fill with null. The full outer join does not support (+) this notation.

Example:

Sql> SELECT * FROM Dave a full join bl bon a.id = b.id;

ID Name ID Name

---------- ---------- ---------- ----------

8 Anqing

1 Dave 1 Dave

2 BL 2 bl

1 BL 1 Dave

2 Dave 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

5 DMM

9 Huaining

9 rows have been selected.

Sql> SELECT * FROM Dave a full outer JOINBL b on a.id = b.ID;

ID Name ID Name

---------- ---------- ---------- ----------

8 Anqing

1 Dave 1 Dave

2 BL 2 bl

1 BL 1 Dave

2 Dave 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

5 DMM

9 Huaining

9 rows have been selected.

• Self-connect

A self-join is a frequently used connection in an SQL statement, and a self-join allows you to treat one mirror of its own table as another table, allowing you to get some special data.

Example:

One of the tables in the Oracle's Scott schema is EMP. Each of the employees in the EMP has their own MGR (manager), and each manager himself is a company employee and 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?

If we have two of these tables teaching workers and MGR, then we can write SQL statements very well.

Select worker.name,mgr.name from Worker,mgrwhere worker.id = mgr.id;

But now we have only one EMP table. So we can use self-connection. The intention of self-connection is to treat a table as more than one table to make a connection. We can write SQL statements like this:

Sql> Select Work.enameworker,mgr.ename Manager from scott.emp work, scott.emp Mgr

2 where work.mgr= Mgr.empno

3 Order by Work.ename;

WORKER MANAGER

---------- ----------

ADAMS SCOTT

ALLEN BLAKE

BLAKE KING

CLARK KING

FORD JONES

JAMES BLAKE

JONES KING

MARTIN BLAKE

MILLER CLARK

SCOTT JONES

SMITH FORD

WORKER MANAGER

---------- ----------

TURNER BLAKE

WARD BLAKE

13 rows have been selected.

A diagram of the various connections: