Four types of SQL connections: left Outer Join, right Outer Join, inner join, and full join.

Four types of SQL connections: left Outer Join, right Outer Join, inner join, and full join.

Four types of SQL connections: left Outer Join, right Outer Join, inner join, and full join

Today, when looking at a data table of a legacy system, we found that the view we usually look for is full out join. As a result, we usually need to perform some restrictive processing on data records, you can also set the right Outer Join for each table in the view and set the sorting and filtering conditions for each column in the view to achieve the effect.

The join condition can be specified in the FROM or WHERE clause. We recommend that you specify the join condition in the FROM clause. The WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the join conditions.

Connections can be divided into the following types:

1. Inner join(Typical join operations use comparison operators such as = or <> ). Including equal join and natural join.
The inner join uses the comparison operator to match rows in two tables based on the values of the columns in each table. For example, retrieve all rows with the same student ID in the students and courses tables.

2. Outer Join.Outer Join can be left Outer Join, right outer join, or complete external join.
When an external join is specified in the FROM clause, it can be specified by one of the following sets of keywords:

1) left join or LEFT OUTER JOIN

The result set of the left outer Join includes all rows in the LEFT table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all selection list columns in the right table in the row of the associated result set are null.

2) right join or RIGHT OUTER JOIN

The right outer join is the reverse join of the left Outer Join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table.

3) full join or FULL OUTER JOIN

The Complete External Join Operation returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.

3. Cross join

Returns all rows in the left table. Each row in the left table is combined with all rows in the right table. Cross join is also called Cartesian product.

Tables or views in the FROM clause can be specified in any order through internal join or complete external join. However, when you use left or right outer join to specify a table or view, the order of tables or views is very important. For more information about using Left or Right outer join to arrange tables, see using outer join.

Example:

-------------------------------------------------
Table a id name table B id job parent_id
1 piece 3 1 23 1
2 Li Si 2 34 2
3 Wang Wu 3 34 4
A. id is related to parent_id

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

1) Internal Connection

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

The result is
1 piece 3 1 23 1
2 Li Si 2 34 2

2) left join

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

The result is
1 piece 3 1 23 1
2 Li Si 2 34 2
3 Wang Wu null

3) Right join

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

The result is

1 piece 3 1 23 1
2 Li Si 2 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 piece 3 1 23 1
2 Li Si 2 34 2
Null 3 34 4
3 Wang Wu null

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!