Several connections for SQL: internal, leftist, right, full, cross-connect

SQL connections can be divided into internal, external, and cross-joins.

Database data:

Book Table Stu Table

1. Internal connection

1.1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the connected column, and its query results list all the columns in the joined table, including the repeating columns.

1.2. No equivalent connection: Use a comparison operator other than the equals operator in the join condition to compare the column values of the connected columns. These operators include >, >=, <=, <,!>,!<, and <>.

1.3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected column, but it uses the selection list to indicate which columns are included in the query result collection and to delete the duplicate columns in the Join table.

INNER JOIN: The INNER JOIN query operation lists the data rows that match the join criteria, which uses comparison operators to compare the column values of the connected columns.

SELECT * FROM book as a,stu as B where A.sutid = B.stuidselect * FROM book as a inner joins Stu as B on a.sutid = B.stuid

The inner connection can be used in both ways, where the inner of the second method can be omitted.

The connection results are as follows A.stuid = B.stuid.

2. External connection

2.1. Left join: is based on left table, A.stuid = B.stuid data is connected, and then the left table does not have a corresponding item, the right table column is null

SELECT * from book as a LEFT join Stu as B on a.sutid = B.stuid

2.2. Right connection: the right table is the benchmark, the A.STUID = B.stuid data is connected, then the right table does not have a corresponding item, the left table column is null

SELECT * FROM book as a right joins Stu as B on a.sutid = B.stuid

2.3. Full connection: A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.

SELECT * from book as a full outer joins Stu as B on a.sutid = B.stuid

3. Cross-Connect

Cross join: Cross joins return all rows from the left table, and each row in the left table is combined with all rows in the right table. Cross joins are also called Cartesian product.

SELECT * from book as a cross join Stu as B order by a.id

Several connections for SQL: internal, leftist, right, full, cross-connect