Usage understanding of various connections of SQL (cross join, INNER join, full join)

connections in SQL can be divided into inner joins, outer joins, and cross connections.

1. Cross join

Without a WHERE clause, it will return the Cartesian product of the two tables connected, and the number of rows returning the result is equal to the product of two table rows;

For example, the following results are the same for a, B, and C, but are not as efficient:

A:select * FROM table1 cross JOIN table2

B:select * from Table1,table2

C:select * FROM table1 a INNER join table2 b

A:select a.*,b.* from table1 a,table2 b where a.id=b.id

B:select * FROM table1 a cross join Table2 b where a.id=b.id ( Note: After cross join conditions can only be used where, cannot be used)

C:SELECT * FROM table1 a inner joins table2 B on A.id=b.id

It is generally not recommended to use methods A and B, because if there is a WHERE clause, the data table of the rows that tend to be the product of the two table row numbers is then selected from the Where condition.

Therefore, if the two tables that require communication are too large, it will be very, very slow and not recommended.

2. internal connection INNER join

Both sides of the table match the combination of conditions

If you use only

SELECT * FROM table1 INNER JOIN table2

If the connection condition is not specified, it is the same as the cross connection of the Cartesian product, but unlike the Cartesian product, the data table of the product of the number of rows of the Cartesian product is not so complicated that the inner connection is more efficient than the cross-connection of the Cartesian product.

However, in general, you need to specify a connection condition using the inner join.

About equivalent connections and natural connections

Equivalent connection (= number applied to the join condition, no duplicate columns are removed)

Natural connection (will remove duplicate columns)

The connection operation for a database is a natural connection, because duplicate rows (tuples) are not allowed to exist.

For example:

SELECT * FROM table1 as a INNER joins Table2 as B on A.column=b.column

3. Outer connection OUTER join

Specifies the inner join of the condition, returning only the entry that meets the join condition.

The outer joins are different, and the returned results include not only rows that meet the join criteria, but also all rows of data that have the left table (when left outer joins), the right table (when connected right), or both sides (when connected at all).

1) left outer connection [OUTER] Join

Displays data rows that match the criteria, while displaying data rows on the left side of the data table that do not match the criteria, with no corresponding entry on the right to display NULL

For example

SELECT * FROM table1 as a left [OUTER] JOIN on A.column=b.column

2) Right-side connection- [OUTER] Join

Displays data rows that match the criteria, while displaying data rows that do not match the criteria on the right side of the table, with no corresponding entry on the left showing NULL

For example

SELECT * FROM table1 as a right [OUTER] JOIN on A.column=b.column

3) Full-Outer connection complete [outer] Join

Displays rows of data that match the criteria, displaying both left and right rows of data that are not eligible, showing null on both sides of the row, which shows the left join of the connection, the correct join, and the INNER join

Usage understanding of various connections of SQL (cross join, INNER join, full join)