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  as  as where = B.stuid Select *  from  as Inner Join  as  on = 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  as  Left Join  as  on = 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  as  Right Join  as  on = 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  as  Full outer Join  as  on = 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  as  Cross Join  as Order  by a.ID

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