SQL connections (internal, external, cross-connect)

SQL connections (internal, external, cross-connect)

Suppose there are two tables: table1, table2

Table1:table2:
ID Name ID Score
1 Lee 1 90
2 Zhang 2 100
4 Wang 3 70

Tables are connected to columns (properties), and two tables are connected by some rule to two tables, typically either by adding columns or by reducing the number of columns, as

ID Name ID Score
1 Lee 1 90
2 Zhang 2 100

1 ) Inner joins: Join or INNER JOIN

SELECT * FROM table 1 join table 2 on two table association part where condition statement

Such as:

SELECT * FROM table1 join table2 on Table1.id=table2.id

An inner join is a join that compares the values of the columns to be joined by comparison operators, and only the rows that match the two tables are connected, he is equivalent

Select a.*,b.* from table1 a,table2 b where a.id=b.id

Results:

ID Name ID Score
1 Lee 1 90
2 Zhang 2 100

2 ) Outer connection (left, right, full connection)

Left Connection: Left join or Left outer join

SELECT * FROM table 1 left JOIN table 2 on two Table association section

Such as

SELECT * FROM table1 LEFT join table2 on Table1.id=table2.id

The result set of the left outer join includes all rows of the left table specified in the OUTER clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null (NULL).

Results

1 Lee 1 90
2 Zhang 2 100
4 Wang Null//left table a row in the right table with no matching row, and the right table fills NULL.

Right-join: Starboard joins or outer join

SELECT * FROM table 1 Right join table 2 on two Table association section

such as SELECT * FROM table1 right join table2 on Table1.id=table2.id

A right outer join is a reverse join of a left outer join. All rows of 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 will be returned for left table.

Results

ID Name ID Score
1 Lee 1 90
2 Zhang 2 100
NULL NULL 3 70//The row in the right table has no matching row in the left table, and the left table has a blank value

Complete outer join: Full join or outer join

SELECT * FROM table 1 full join table 2 on two Table association section

Such as

SELECT * FROM table1 full join table2 on Table1.id=table2.id

A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.
Results

ID Name ID Score
1 Lee 1 90
2 Zhang 2 100
4 Wang Null NULL
NULL NULL 3 70//a table with no matching rows, another table fill value

3 ) Cross-connect ( complete, cartesian product ) :  Cross join ( without on condition where ...)

SELECT * FROM table 1 cross Join table 2

such as SELECT * FROM table1 cross join Table2

A cross join without a WHERE clause (or an ON clause) will produce a Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set. (Table1 and table2 cross-joins generate 3*3=9 Records), he is equivalent to select * from Table1,table2

Results
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

SQL connections (internal, external, cross-connect)