What is the difference between inner join and outer join?

Difference: the inner join query operation lists the data rows that match the join conditions; the outer join, returns to the query result set not only contains the rows that meet the join conditions, but also includes the left table (left outer join) , all data rows in the right table (right outer join), or both edge join tables (full outer join).

The connection of two tables is established by linking one or more columns in one table with columns in the other table. The expressions used to join two tables form the join condition. When the connection is successful, the data in the second table is connected with the first table, and a composite result set is formed-including the plan of the data rows in the two tables. Simply put, there are subsets of the two tables, although only temporarily

There are two basic types of connections, inner and outer connections. The main difference between the two types is that an outer join will return rows in the result set even if the join conditions are not satisfied, while an inner join will not return rows in the result set class

When the external connection does not meet the connection conditions, it usually returns a column in one table, but there is no return value in the second table — it is null

(1) Inner connection

The inner join query operation lists the data rows that match the join condition, and it uses comparison operators to compare the column values ​​​​of the joined columns. There are three types of inner joins:

1. Equal value join: Use the equal sign (=) operator in the join condition to compare the column values ​​of the joined columns, and the query results list the values ​​in the joined table All columns, including duplicates within them.

2. Unequal connection: Use comparison operators other than the equal operator in the connection condition to compare the column values ​​​​of the columns to be connected. These operators include >, >=, <=, , !<, and .

3. Natural connection: Use the equal (=) operator in the connection condition to compare the column values ​​​​of the connected columns, but it uses the select list to indicate the columns included in the query result set and delete the connection table Repeated columns in .

(2) Outer join

Outer join, returned to the query result set not only contains rows that meet the join conditions, but also includes the left table ( Left outer join or left join)), right table (right outer join or right join) or all data rows in both edge join tables (full outer join).

left join (left join) returns records including all records in the left table and the records with the same join field in the right table;

right join (right join) returns records including all records in the right table All records and records with the same join field in the left table;

Example 1:

SELECT a.*,b.* FROM luntan  LEFT JOIN usertable as b
 ON a.username=b.username

For example 2:

SELECT a.*,b.*
 FROM city as a FULL OUTER JOIN user as b
 ON a.username=b.username

For more programming knowledge, please visit: Programming Learning! !

The above is the difference between inner join and outer join? For more details, please pay attention to other related articles on 1024programmer.com!