Use of joins in SQL

Direct examples to illustrate the various methods:

CREATE TABLE A (ID int,name varchar) CREATE TABLE B (ID int,name varchar) insert INTO B VALUES (1, ' Zhang San ') insert INTO B VALUES (2, ' John Doe ') insert into B values (3, ' Harry ') insert into B values (4, ' Caifan ') insert into B values (5, ' von VII ') insert into a values (1, ' Zhao Eight ') insert into a values (2, ' Zhang San ') insert into a values (3, ' von VII ') insert into a values (4, ' Tian Nine ') insert into a values (5, ' Ling Ten ')

First type: Inner join

Explanation: The resulting result is the intersection of A and B (the same value in the same column). An inner join is the most common connection, also known as a normal connection, that connects only matching rows (only the columns in cross that meet the conditions of the join). It is also divided into an equivalent connection (the Join condition operator is "=") and a non-equivalent connection (the Join condition operator is not "=", for example Between...and).

SELECT *from b INNER JOIN Aon b.name=a.name

Second type: Full Outer Join

Explanation: The resulting result is the set of A and B (if no same value is null as the value). Full JOIN returns all rows from the left and right tables. If the data row for one of the tables does not have a matching row in the other table, the opposite data is replaced with null

SELECT * from B full OUTER JOIN Aon b.name=a.name

The third kind: Left outer join

Explanation: A complete set of table B is generated, and a match in table A has a value (the null value is substituted if there is no match). The LEFT join returns all rows from the right table and rows that satisfy the on condition, and if the rows in the left table do not match in the right table, the corresponding data in the right table of the row is replaced with null.

SELECT * from B left outer join Aon B.name=a.name

Fourth type: Right Outer Join

Explanation: Refer to the third left outer join

SELECT * from B right Outer Join Aon b.name=a.name

5th Type: Cross Join

Explanation: Cross performs a Cartesian product on two tables. It returns one row for each possible combination of the left and right table rows. Returns the table (the number of rows in the left table row) row of rows. PS: This syntax is seldom used. Note that if you do not use a nested SELECT statement, the general system will produce a Cartesian product and then filter. This is very dangerous for performance, especially when the table is very large.

SELECT * from B cross  join A

Special: Natural Join (Oracle usage, SQL Server does not support this notation.) PS: Great God can correct usage here)

Explanation: A natural connection is a special equivalent connection that uses the equals (=) operator in the join condition to compare the column values of the connected column, but uses a selection list to indicate the columns included in the query result collection and to delete the duplicate columns in the Join table.

Use of joins in SQL