What is the difference between inner join and outer join?

Difference: Inner join query operation lists data rows that match the join conditions; outer join, the query result set returned not only includes 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 two side tables (full outer join).

#The connection between two tables is established by linking one or more columns in one table with columns in another table. The expression used to join the two tables forms 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 - a plan that includes the data rows in the two tables. To put it simply, the two tables have a subset, although it is only temporary.

There are two basic types of connections, inner and outer connections. The main difference between the two types is that the outer join will return rows in the result set even if the join conditions are not met, while the 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. It uses comparison operators to compare the column values ​​of the joined columns. There are three types of inner joins:

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

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

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

(2) Outer join

Outer join, the query result set returned not only includes rows that meet the join conditions, but also the left table (left outer join or left join)), right table (right outer join or right join), or two side tables (full outer join) all data rows.

Left join (left join) returns all records in the left table that are equal to the join fields in the right table;

right join (right join) returns all records in the right table Records that are equal to the join field in the left table;

For 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-related knowledge, please visit: Programming Learning ! !

The above is the detailed content of What is the difference between inner join and outer join?. For more information, please follow other related articles on the PHP Chinese website!