Database Connection Learning

Connections can be divided into the following types:

• Inner join (a typical join operation that uses 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, searchStudentsAndCoursesAll rows with the same student ID.

Rows are returned only when at least one row in the same two tables meets the join conditions. The inner join removes rows that do not match any row in the other table.

• Outer Join. Outer Join can be left Outer Join, right outer join, or complete external join. The outer join will return all rows of at least one table or view mentioned in the from clause.

When an external join is specified in the from clause, it can be specified by one of the following sets of keywords:

• 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.

• 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.

• 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.

• 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. The result set is M * n.

 

Example:

-- Create Table Table1, Table2:
Create Table Table1 (ID int, name varchar (10 ))
Create Table Table2 (ID int, score INT)
Insert into Table1 select 1, 'lil'
Insert into Table1 select 2, 'zhang'
Insert into Table1 select 4, 'wang'
Insert into Table2 select 1, 90
Insert into Table2 select 2,100
Insert into Table2 select 3, 70

 

Inline:

Select * From Table1 join Table2 on table1.id = table2.id
------------- Result -------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 2 100
------------------------------
Note: Only the Table1 and Table2 columns that meet the conditions are returned.

 

Left join:

Select * From Table1 left join Table2 on table1.id = table2.id
------------- Result -------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 2 100
4 Wang null
------------------------------
Note: all the clauses containing Table 1 return the corresponding fields of Table 2 based on the specified conditions. The non-conforming fields are displayed as null.

 

Right join:

Select * From Table1 right join Table2 on table1.id = table2.id
------------- Result -------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 2 100
Null null 3 70
------------------------------
Note: all the clauses containing Table 2 return the corresponding fields of Table 1 Based on the specified conditions. The non-conforming fields are displayed as null.

 

Complete External Connection:

Select * From Table1 full join Table2 on table1.id = table2.id
------------- Result -------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 2 100
4 Wang null
Null null 3 70
------------------------------
Note: returns the sum of left and right connections (see upper left and right connections)

 

Cross join:

Select * From Table1 cross join Table2
------------- Result -------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 1 90
4 wang 1 90
1 Lee 2 100
2 Zhang 2 100
4 Wang 2 100
1 Lee 3 70
2 Zhang 3 70
4 Wang 3 70
------------------------------
Note: 3*3 = 9 records are returned, that is, Cartesian product.