Article From:https://www.cnblogs.com/yirgaMonkey/p/9216756.html

SQLConnection can be divided into internal connection, external connection and cross connection.

 

Database data:

           

bookTable stu table

 

1.Internal connection

1.1.Equivalent connection: the column value of the connected column is compared with the equal number (=) operator in the connection condition, and the query result lists all the columns in the connected table, including the repeated columns in it.

1.2.Unequal value connection: the column value of the column that is connected to the join condition by using other comparison operators other than operator. These operators include > > =, < =, <;! >; < and < >

1.3.Natural connection: using the equal (=) operator to compare the column values of the connected column in the connection condition, but it uses the selection list to point out the columns included in the query result set and delete the repeated columns in the connection table.

Internal connection: the internal join query operation lists the data rows that match the join conditions. It compares the column values of the connected columns with the comparison operators.

select * from book as a,stu as b where a.sutid = b.stuid

select * from book as a inner join stu as b on a.sutid = b.stuid

Internal connection can use the above two ways, of which second ways of inner can be omitted.

The result of the connection is as follows: it is connected according to a.stuid = b.stuid.

 

2.External connection

2.1.Left join: connect the data of a.stuid = b.stuid based on the left table, then display the corresponding item in the left table, and the column in the right table is NULL.

select * from book as a left join stu as b on a.sutid = b.stuid

2.2.Right connection: Based on the right table, the data of a.stuid = b.stuid are connected, but the corresponding items in the right table are displayed, and the left table is NULL.

select * from book as a right join stu as b on a.sutid = b.stuid

2.3.Full connection: the complete external join returns all rows in the left table and the right table. When a row does not match rows in another table, the list of columns in another table contains null values. If there is a matching row between the tables, the entire result set contains the data value of the base table.

select * from book as a full outer join stu as b on a.sutid = b.stuid

 

3.Cross connection

Cross connect: cross join returns all rows in the left table, and each row in the left table is combined with all rows in the right table. Cross join is also called Cartesian product.

select * from book as a cross join stu as b order by a.id

Link of this Article: SQL statement connection

Leave a Reply

Your email address will not be published. Required fields are marked *