SQL Four connection-left outer, right outer, inner, full connection detailed _mysql

Four types of connections to SQL-left outer, right outer, inner, fully connected

Today, when I look at a data table for a legacy system, I find that the view we are looking for is full out join. Cause the usual data records to do some restrictive processing, you can also set the view tables to the right outer joins and set the columns on the view sorting and filtering criteria to achieve the effect.

The join condition can be specified in the From or WHERE clause, and it is recommended that the join condition be specified in the FROM clause. The WHERE and having clauses can also contain search conditions to further filter the rows selected by the join condition.

Joins can be grouped into the following categories:

1. Inner joins (typical join operations, using comparison operators such as = or <>). Includes equal joins and natural joins.
Inner joins use comparison operators to match rows in two tables based on the values of the columns that are common to each table. For example, retrieve all lines that are the same as the student identification number for the students and courses tables.

2, outer joint. an outer join can be a left outer join, a right outer join, or a full outer join.
When you specify an outer join in the FROM clause, you can specify one of the following groups of keywords:

1) left JOIN or left OUTER join

The result set of a left outer join consists of all the rows of the left table specified in the outer clause, not just the rows that the join columns match. If a row in the left table does not have a matching row in the right table, all picklist columns in the right table in the associated result set row are null values.

2 Right Join or right OUTER join

A right outer join is a reverse join of a left outer join. All rows from the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value is returned for left table.

3 full Join or full OUTER join

A full outer join returns all rows in the left and right tables. When a row does not match rows in another table, the select list column for the other table contains null values. If there is a matching row between the tables, the entire result set row contains the data values for the base table.

3. Cross Join

A cross join returns all the rows in the left table, with each row in the left table combined with all the rows in the right table. Cross joins are also called Cartesian product.

A table or view in the FROM clause can be specified in any order by an inner join or a full outer join, but the order of the table or view is important when the table or view is specified with a left or right outer join. For more information about using a left or right outer join to arrange tables, see Using outer joins.

Example:

-------------------------------------------------
A table ID name B table ID job parent_id
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu 3 34 4
The relationship between a.ID and parent_id

--------------------------------------------------

1) Inner Connection

Select a.*,b.* from a inner join B on a.id=b.parent_id

The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2

2) Left Connection

Select a.*,b.* from a LEFT join B on a.id=b.parent_id

The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu Null

3) Right Connection

Select a.*,b.* from right join B on a.id=b.parent_id

The result is

1 Sheets 3 1 23 1
2 Lee 42 34 2
Null 3 34 4

4) Full Connection

Select a.*,b.* from a full join B on a.id=b.parent_id

The result is

1 Sheets 3 1 23 1
2 Lee 42 34 2
Null 3 34 4
3 Wang Wu Null

Thank you for reading, I hope to help you, thank you for your support for this site!