SQL Join Usage

With regard to the JOIN keyword in the SQL statement, which is a more common and less understandable keyword, the following example gives a simple explanation-build table Table1,table2:
CREATE TABLE table1 (ID int,name varchar (10))
CREATE TABLE table2 (ID int,score int)
INSERT INTO table1 Select 1, ' Lee '
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
such as table
-------------------------------------------------
Table1 | table2 |
-------------------------------------------------
ID Name |id Score |
1 Lee | 90 |
2 Zhang 100 |
4 Wang |3 70 |
-------------------------------------------------

The following are performed in Query Analyzer

One, outer connection
1. Concept: Includes a LEFT outer join, a right outer join, or a full outer join

2. Left-side connection: outer JOIN
(1) 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).
(2) SQL statements
SELECT * FROM table1 LEFT join table2 on Table1.id=table2.id
-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 2 100
4 Wang Null NULL
------------------------------
NOTE: All clauses that contain table1, return table2 corresponding fields according to specified criteria, non-conforming null display

3. Right Join
(1) 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.
(2) SQL statements
SELECT * FROM table1 right join table2 on Table1.id=table2.id
-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 2 100
NULL NULL 3 70
------------------------------
NOTE: All clauses that contain table2, return table1 corresponding fields according to specified criteria, non-conforming null display

4. Complete outer join: Full JOIN or outer join
(1) 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.
(2) SQL statements
SELECT * FROM table1 full join table2 on Table1.id=table2.id
-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 2 100
4 Wang Null NULL
NULL NULL 3 70
------------------------------
Note: Returns the left and right connected and (see top

Second, internal connection
1. Concept: An inner join is a join that compares the values of the columns to be joined by comparison operators

2. Internal connection: Join or INNER JOIN

3.sql statements
SELECT * FROM table1 join table2 on Table1.id=table2.id
-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 2 100
------------------------------
Note: only table1 and table2 columns that match the criteria are returned

4. Equivalent (same as the following execution effect)
A:select a.*,b.* from table1 a,table2 b where a.id=b.id
B:select * FROM table1 Cross join Table2 where table1.id=table2.id (note: The Add condition after cross join can only be used where, cannot be used)

Three, cross-connect (full)

1. Concept: A cross join without a WHERE 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)

2. Cross-Joins: crosses join (without conditions where ...)

3.sql statements
SELECT * FROM table1 cross join 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
------------------------------
Note: Returns the 3*3=9 record, which is the Cartesian product

4. Equivalent (same as the following execution effect)
A:select * from Table1,table2

SQL Join Usage