SQL table Connection inner JOIN, full join, left JOIN, right join, natural join

One, internal connection-inner jion:

SELECT * FROM table1 INNER JOIN table2 on table1.field1 compopr table2.field2

The INNER JOIN operation can be divided into the following sections:

Section description

Table1, Table2 records the name of the table being combined.

Field1, field2 the name of the field being joined. If they are not made of numbers, the fields must be of the same data type and contain homogeneous data, but they do not need to have the same name.

Select S.name,m.mark from student S,mark m where S.id=m.studentid

Select S.name,m.mark from student s inner join Mark M on S.id=m.studentid

Second, left join connect-left join:

A connection can usually be established in the FROM clause or a WHERE clause in the SELECT statement, in the following syntax format:

From Join_table Join_type join_table

[On (Join_condition)]

Where join_table indicates the name of the table participating in the JOIN operation, the connection can operate on the same table, or multiple table operations, the connection to the same table operation is called self-join, Join_type is a connection type, can be a left join or a outer join, or a inner join.

On (join_condition) is used to refer to the join condition, which consists of columns and comparison operators, logical operators, etc. in the connected table.

Left join is to select all the tuples from the left table:

Select S.name,m.mark from student s left join Mark M on S.id=m.studentid

Third, right connection-right join:

A connection can usually be established in the FROM clause or a WHERE clause in the SELECT statement, in the following syntax format:

From Join_table Join_type join_table

[On (Join_condition)]

Where join_table indicates the name of the table participating in the JOIN operation, the connection can be operated on the same table, or multiple table operations can be done. A connection to the same table operation is called a self-join.

On (join_condition) is used to refer to the join condition, which consists of columns and comparison operators, logical operators, etc. in the connected table.

The right connection is to remove all data from the right table, regardless of whether the table on the left has matching data:

Select S.name,m.mark from student s right join Mark M on S.id=m.studentid

Iv. fully connected-full join:

Using the format as above has been explained above

Remove the data from the left and right two tables, whether or not they match:

Select S.name,m.mark from student s full join Mark M on S.id=m.studentid

v. Natural connection-natural Join

There's only one common item in the middle of the two tables and it's what we need to match.

We can use natural links directly

From Dept_emp D NATURAL JOIN Employees E

No need to add matching criteria yourself

SQL table Connection inner JOIN, full join, left JOIN, right join, natural join