Here are two tables TableA and TableB, the name table and the age table, for the test data for our example TableA
TableB
In the development of our business requirements are sometimes complex, multiple table joint query when there are many ways, in the face of different needs, flexible use of different table connection, then the table connection into Which?
How many tables are connected?SQL table Join partitioning
External Connection、
Internal ConnectionAnd
Cross Connect.
I. External ConnectionsOverview: The outer connection includes three kinds, namely the left outer connection, the right outer connection, the whole outer connection. Corresponding SQL keyword: left/right/full OUTER join, usually we omit the OUTER keyword, written as a left/right/full join. In the left and right outer joins, a table is the base table, all the rows and columns of the base table are displayed, and all the outer column values are NULL if the condition does not match. Full outer joins all rows and columns of the table are displayed, and the condition mismatch value is null. 1. Left OUTER Connection example: SQL statement: SELECT * from TableA-TableB on tablea.id=tableb.id result:
| Id |
Name |
Id |
Age |
| 1 |
T1 |
1 |
18 |
| 2 |
T2 |
2 |
20 |
| 4 |
T4 |
Null |
Null |
Note: All rows and columns in TableA (base table) are displayed, and the third row of conditions does not match all TableB (appearances) values are null.
2. Right-Outer Connection example: SQL statement: SELECT * from TableA R join TableB on Tablea.id=tableb.id result:
| Id |
Name |
Id |
Age |
| 1 |
T1 |
1 |
18 |
| 2 |
T2 |
2 |
20 |
| Null |
Null |
3 |
19 |
Note: All rows and columns in TableB (base table) are displayed, and the third row of conditions does not match all TableA (appearances) values are null. 3. Full OUTER JOIN example: SQL statement: SELECT * from TableA full join TableB on Tablea.id=tableb.id result:
| Id |
Name |
Id |
Age |
| 1 |
T1 |
1 |
18 |
| 2 |
T2 |
2 |
20 |
| 3 |
T4 |
Null |
Null |
| Null |
Null |
3 |
19 |
Note: All rows and columns of TableA and TableB are displayed, and the value of the row for which the condition does not match is null
two. Internal Connection
Overview: An inner join is a connection that compares the values of the columns to be concatenated with comparison operators, and the mismatched rows are not displayed. SQL keyword join or inner join, usually we write the join example: SELECT * from TableA join TableB on tablea.id=tableb.id result:
| Id |
Name |
Id |
Age |
| 1 |
T1 |
1 |
18 |
| 2 |
T2 |
2 |
20 |
Note: Only the rows that return the criteria match are written as equivalent to: SELECT * from Tablea,tableb where tablea.id=tableb.id select * from TableA Cross join TableB W Here Tablea.id=tableb.id (after cross joins can only be used where not on)
three. Cross-connect
Concept: A cross join without a where condition will produce the Cartesian product involved in the join table. That is, the result set of the number of rows *tableb the TableA number of rows. (TableA 3 Rows *tableb 3 rows =9 rows) SQL statement: SELECT * from TableA Cross join TableB Result:
| Id |
Name |
Id |
Age |
| 1 |
T1 |
1 |
18 |
| 2 |
T2 |
1 |
18 |
| 4 |
T4 |
1 |
18 |
| 1 |
T1 |
2 |
20 |
| 2 |
T2 |
2 |
20 |
| 4 |
T4 |
2 |
20 |
| 1 |
T1 |
3 |
19 |
| 2 |
T2 |
3 |
19 |
| 4 |
T4 |
3 |
19 |
Note: Returns the 3*3=9 row data, which is the Cartesian product. The above notation is equivalent to: SELECT * from Tablea,tableb reference article: http://323229113.blog.163.com/blog/static/2185362820070172553209/
PS: This is entirely a point, I write bad, I hope the great God can be a lot of guidance, common discussion, common learning.
Several ways to join SQL tables
