SQL internal, external, and self-connected queries

I. Preface:

Typically, queries on tables in a project are associated with multiple tables, and multi-table queries involve intra-and outer-join and self-connected queries of SQL. This article will briefly describe the use of these SQL connections, we hope to help.

Two. Data preparation:

Prepare two tables First:

1. Student Table: Student

SELECT * from student;

2. Teacher's Table: Teacher

SELECT * from teacher;

Three. Related queries:

1. Internal connections: find common records in each table that meet the criteria. [x inner join Y on ...]

The first method of writing: (use where only)
Select T.teacher_name, s.student_name from teacher t,student s where t.id = s.teacher_id;

The second way: (Join.. On: ）
Select T.teacher_name, s.student_name from teacher T joins student s on t.id = s.teacher_id;

The third type of notation:(inner join ... )
Select T.teacher_name, s.student_name from teacher T Inner joins student s on t.id = s.teacher_id;

2. External connection

There are three ways to connect outside: Left, right, and full.

2.1 Left Connection: Based on the records of the left table, find matching records in the connected right table, and if no match to the left table is found, it is represented by NULL. [x left [outer] join Y on ...

The first one is: (left join.. On: ）

Select T.teacher_name, s.student_name from teacher T left joins student s on t.id = s.teacher_id;

The second way: (left outer join). On: ）

Select T.teacher_name, s.student_name from teacher T left outer joins student s on t.id = s.teacher_id;

The Third Way: "(+)" Where the other side of the position is the direction of the connection

Select T.teacher_name, s.student_name from teacher T, student s where t.id = s.teacher_id (+);

2.2 Right Connection: Based on the records of the right table, find the matching records in the connected left table, and if no match is found, fill with null. [x right [outer] join Y on ...]

The first one: ()

Select T.teacher_name, s.student_name from teacher T right joins student s on t.id = s.teacher_id;

The second way:

Select T.teacher_name, s.student_name from teacher T right outer joins student s on t.id = s.teacher_id;

The Third Way: "(+)" Where the other side of the position is the direction of the connection

Select T.teacher_name, s.student_name from teacher T, student s where t.id (+) = s.teacher_id;

2.3 Full Connection: Returns the records of all tables that meet the criteria, without matching them, with a null representation (the result is a set of left and right joins)

The first form of writing: (Full join.. On: ）

Select T.teacher_name, s.student_name from teacher T full join student s on t.id = s.teacher_id;

The second way: (Full outer join. On

Select T.teacher_name, s.student_name from teacher T full outer joins student s on t.id = s.teacher_id;

3. Self-connect

Self-connected, connected to the two tables are the same table, the same can be connected by the internal, external connection of a variety of combinations, according to the actual application to combination.

SELECT a.*, b.* from table_1 a,table_1 b WHERE a.[name] = B.[name]

SQL internal, external, and self-connected queries