Differences between inner connection and outer connection

(1) inner connection
The inner join query operation lists the data rows that match the connection condition. It uses the comparison operator to compare
Column value. Intranet connections are divided into three types:
1. equijoin: Use the equal sign (=) operator in the connection condition to compare the column values of the connected column.
If this parameter is set, all columns in the connected table, including duplicate columns, are listed.
For example, the following uses equijoin to list authors and publishers in the same city in the authors and publishers tables:
Select *
From authors asInnerJoinPublishers as P
On a. City = P. City
2. Unequal join: Use a comparison operator other than the equal operator to compare the connected
Column value. These operators include >,>=, <=, <,!> ,! <And <>.
3. Natural join: Use the equal to (=) operator in the connection condition to compare the column values of the connected column, but it uses
Select the list to indicate the columns included in the query result set and delete the duplicate columns in the connection table.
For example, in the select list, delete the duplicate columns (city and state) in the authors and publishers tables ):
Select a. *, P. pub_id, P. pub_name, P. Country
From authors asInnerJoinPublishers as P
On a. City = P. City
(2) External Connection
The returned results include not only the rows that meet the connection conditions, but also the left table (left
All data rows in the outer join or left join table), right table (right Outer Join or right join), or two edge join tables (all Outer Join.
Left join returns records that include all records in the left table and join fields in the right table;
Right join returns records that include all records in the right table and join fields in the left table;
Example 1: select a. *, B. * From luntan leftJoinUsertable as B
On a. Username = B. Username
Example 2: select a. *, B .*
From City as a full outerJoinUser as B
On a. Username = B. Username
(3) cross join
The cross join clause does not contain the WHERE clause. It returns the Cartesian product of all data rows in the two joined tables and returns
The number of rows in the result set is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.
The number of data rows under the query condition. For example, there are 6 types of books in the titles table, and eight publishers in the publishers table
The number of records retrieved by column crossover is 6*8 = 48 rows.
Example: select type, pub_name
From titles crossJoinPublishers
Order by type