SQL-Internal and external connections

PDF:sql-inside and outside connections. PDF

The connection query is often used in the relational database, and it is the basis of the multi-table joint query.
Mainly includes: inner connection , outer connection , Cross connection .

• SQL-Internal and external connections
  • Internal connection
    • Equivalent connection
    • No equivalent connection
    • Natural connection
  • External connection
    • Left connection
    • Right connection
    • Fully connected
  • Cross Connect

Internal connection

The inner connection is also divided into equivalent connection , not equivalent connection , natural connection .

The comparison operators used in connection queries are: =, <, <>, >=, <=,!>,!<

Equivalent connection

The equivalent connection uses "=" to perform the comparison operation.
Take a look at the following example:

student_id	Student_name	class_id
1	Aaa	15
2	Bbb	16
3	Ccc	17

class_id	class_name
15	Class Five
16	Class Six
17	Class Seven
18	Class Eight

If you need to find out something:

Class name for each student

The following SQL statements are available at this time:

SELECT * FROM
    T_student ts,T_class tc
WHERE
    ts.class_id=tc.class_id

Or:

SELECT * FROM
    T_student ts inner join T_class tc
ON
    ts.class_id = tc.class_id

The query results are as follows:

student_id	Student_name	class_id	class_id	class_name
1	Aaa	15	15	Class Five
2	Bbb	16	16	Class Six
3	Ccc	17	17	Class Seven

The conclusions are as follows:

Equivalent connections:

To join tables T1 and T2, the comparison condition is t1.a=t2.a, and the database is compared to a in the T1 for all elements of column A in T2, and if they are equal, the row is output.

As for how the database is implemented internally, we are not going to delve into it.

No equivalent connection

Within a connection, a "=" is not used as the comparison operator and is called a non-equivalent connection.
If you need to query the following:

T_student tables and T_class tables, all combinations of class_id fields are not equal

The following SQL statements are available at this time:

SELECT * FROM
    T_student ts,T_class tc
WHERE
    ts.class_id <> tc.class_id

Or:

SELECT * FROM
    T_student ts inner join T_class tc
ON
    ts.class_id <> tc.class_id

The query results are as follows:

student_id	Student_name	class_id	class_id	class_name
2	Bbb	16	15	Class Six
3	Ccc	17	15	Class Seven
1	Aaa	15	16	Class Five
3	Ccc	17	16	Class Seven
1	Aaa	15	17	Class Five
2	Bbb	16	17	Class Six
1	Aaa	15	18	Class Five
2	Bbb	16	18	Class Six
3	Ccc	17	18	Class Seven

The conclusions are as follows:

No equivalent connection

To join tables T1 and T2, where the comparison condition is t1.a not equal to t2.a, then the database is compared to a in T1 for all elements in column A, and if not equal, the row is output.

Natural connection

The natural connection is a special equivalent connection, similar to the equivalent connection, the difference is:

A natural connection removes duplicate columns;
The natural connection requires the same two column properties to be compared, and the equivalent connection is not required;

If you need to find the following:

Class name for each student

The SQL statement is similar to the equivalent connection:

SELECT ts.*,tc.class_name FROM
    T_student ts inner join T_class tc
ON
    ts.class_id = tc.class_id

The query results are as follows:

student_id	Student_name	class_id	class_name
1	Aaa	15	Class Five
2	Bbb	16	Class Six
3	Ccc	17	Class Seven

Thus:

The natural connection is equivalent to the equivalent of the connection, plus the display of the qualification, so that the column to weight

External connection

The outer connection is divided into left connection , right connection and full connection .

Left connection

Left join is based on left table, showing all records in the left table (number of records displayed = number of bars recorded in the left table). The specified column in the left table is then compared with the specified columns in the right table, the output value is satisfied, the output NULL is not satisfied
If you need to find the following:

Class name for each student

The following SQL statements are available at this time:

SELECT * FROM
    T_student ts left join T_class tc
ON
    ts.class_id = tc.class_id

The query results are as follows:

student_id	Student_name	class_id	class_id	class_name
1	Aaa	15	15	Class Five
2	Bbb	16	16	Class Six
3	Ccc	17	17	Class Seven

But if you add a field to the T_student table:

student_id	Student_name	class_id
4	Ddd	20

At this point, with the previous SQL statement query, the result becomes:

student_id	Student_name	class_id	class_id	class_name
1	Aaa	15	15	Class Five
2	Bbb	16	16	Class Six
3	Ccc	17	17	Class Seven
4	Ddd	20	NULL	NULL

The conclusions are as follows:

Left connection

Based on left table, the number of record bars displayed = number of records in the left table. If the selected field in the left table matches the criteria, the displayNULL

Right connection

The opposite of left join. Based on the right table, show the number of records = number of records in the right table, then compare with the fields in the left table, and then output if the conditions are true NULL .
Use the following SQL statement:

SELECT * FROM
    T_student ts right join T_class tc
ON
    ts.class_id = tc.class_id

The query results are:

student_id	Student_name	class_id	class_id	class_name
1	Aaa	15	15	Class Five
2	Bbb	16	16	Class Six
3	Ccc	17	17	Class Seven
NULL	NULL	NULL	18	Class Eight

The conclusions are as follows:

Right connection

Based on the right table, show the number of records in the record = number of record bars in the right table. If the selected field in the left table matches the criteria, the displayNULL

Fully connected

Full join is similar to the combination of left and right connections, showing the number of records = Specifies the number of different kinds of comparison fields in two tables. is displayed for the free field NULL .
Use the following SQL statement:

SELECT * FROM
    T_student ts full join T_class tc
ON
    ts.class_id = tc.class_id

The query results are:

student_id	student_name	class_id	class_id	class_name
1	aaa	15	15	class five
2	bbb	16	16	class six
3	CCC	17	17	class seven
NUL L	NULL	NULL	18	Eight classes

But if you add a field to the T_student table:

student_id	Student_name	class_id
4	Ddd	20

At this point, with the previous SQL statement query, the result becomes:

student_id	Student_name	class_id	class_id	class_name
1	Aaa	15	15	Class Five
2	Bbb	16	16	Class Six
3	Ccc	17	17	Class Seven
4	Ddd	20	NULL	NULL
NULL	NULL	NULL	18	Class Eight

The conclusions are as follows:

Fully connected

Specifies the number of different kinds of comparison fields in two tables. is displayed for the free field NULL .

Cross Connect

Cross Connect is simple, that is, two tables to do 笛卡尔积 .
If you do not add a where selection comparison, the number of record rows displayed is the product of the two table row numbers.
Use the following SQL statement:

SELECT * FROM
    T_student cross join T_class

Or:

SELECT * FROM
    T_student,T_class

The query results are:

student_id	Student_name	class_id	class_id	class_name
1	Aaa	15	15	Class Five
2	Bbb	16	15	Class Six
3	Ccc	17	15	Class Seven
1	Aaa	15	16	Class Five
2	Bbb	16	16	Class Six
3	Ccc	17	16	Class Seven
1	Aaa	15	17	Class Five
2	Bbb	16	17	Class Six
3	Ccc	17	17	Class Seven
1	Aaa	15	18	Class Five
2	Bbb	16	18	Class Six
3	Ccc	17	18	Class Seven

If there is a where selection, then the Cartesian product is first performed and then selected in the results of the Cartesian product (poor efficiency).
The conclusions are as follows:

Cross Connect

No where selection is made, the result is a Cartesian product of two tables
Have a where choice, do the Cartesian product first, choose the result of the Cartesian product

• Github: @crazyacking, Devin
• Email:[email protected]

SQL-Internal and external connections