SQL JOIN usage 1

The join keyword in SQL statements is a commonly used and not easy to understand keyword. The following example provides a simple explanation and I believe it will be helpful to you.

-- 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
Such as table
-------------------------------------------------
Table1 | table2 |
-------------------------------------------------
Id name | id score |
1 lee | 1 90 |
2 zhang | 2 100 |
4 wang | 3 70 |
-------------------------------------------------

Run the following commands in the query Analyzer:

1. External Connection
1. Concept: including left Outer Join, right Outer Join or complete external join

2. left join: left join or left outer join
(1) 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 the selection list columns in the right table in the row of the associated result set are null ).
(2) SQL statements
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.

3. right join: right join or right outer join
(1) 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.
(2) SQL statements
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 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.

4. Complete External join: full join or full outer join
(1) The Complete External join 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.
(2) SQL statements
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 3 70
------------------------------
Note: returns the sum of left and right connections (see upper left and right connections)

2. Internal Connection
1. Concept: inner join is a join that uses a comparison operator to compare the values of the columns to be joined.

2. inner join: join or inner join

3. SQL statements
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.

4. equivalent (same as the following execution)
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: cross join can only use where, not on)

Iii. Cross-join (complete)

1. Concept: A cross join without a WHERE clause will generate the 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 is equal to the size of the Cartesian result set. (Table1 and table2 generate 3*3 = 9 records)

2. cross join: cross join (without the condition where ...)

3. SQL statements
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.

4. equivalent (same as the following execution)
A: select * from table1, table2

1.
A. UNION
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2

B. Intersection JOIN
SELECT * FROM table1 AS a JOIN table2 B ON a. name = B. name

C. difference set NOT IN
SELECT * FROM table1 WHERE name not in (SELECT name FROM table2)

D. Cartesian Product
SELECT * FROM table1 cross join table2
And
SELECT * FROM table1, table2 is the same

2. UNION in SQL
The difference between UNION and union all is that the former removes duplicate entries and the latter retains them.

A. UNION
SQL Statement1
UNION
SQL Statement2

B. UNION ALL
SQL Statement1
UNION ALL
SQL Statement2

3. Various joins in SQL
SQL connections can be divided into internal connections, external connections, and cross connections.

(That is, Cartesian Product)

A. CROSS JOIN
Without the WHERE clause, it returns the Cartesian product of the two joined tables, and the number of rows returned is equal to the product of the number of rows of the two tables;

Example
SELECT * FROM table1 cross join table2
Equivalent
SELECT * FROM table1, table2

It is generally not recommended to use this method, because if there is a WHERE clause, the data table will always be the data table of the rows of the two tables multiplied by the number of rows before selection based on the WHERE condition.
Therefore, if two tables that require communication are too large, they will be very slow and are not recommended.

B. INNER JOIN
If you only use
SELECT * FROM table1 inner join table2
If no connection condition is specified, the result is the same as that of the cross join operation.

However, when using inner join, you must specify the connection conditions.
-- Equijoin (= applies to connection conditions, and duplicate columns are not removed)
SELECT * FROM table1 AS a inner join table2 AS B on a. column = B. column
-- Unequal connection (>,>=,<,<=,!> ,! <, <>)
For example
SELECT * FROM table1 AS a inner join table2 AS B on a. column <> B. column
-- Natural join (repeated columns are removed)

C. OUTER JOIN
First, the difference between the internal connection and the external connection is as follows:
If no join condition is specified for an inner join, the result of the cross join is the same as that of the Cartesian product. However, unlike the Cartesian product, the data table is not as complex as the Cartesian product, the internal join efficiency is higher than that of cartesian products.

Only entries that meet the connection conditions are returned.
The outer join is different. The returned results include not only the rows that meet the connection conditions, but also the left table (when the left Outer Join is performed) and the right table (when the right join is performed) or all data rows connected by both sides (when all external connections.

1) left outer join left [OUTER] JOIN
Displays the rows that meet the condition, and displays the rows that do not meet the condition in the left table. No corresponding entries on the right show NULL.
For example
SELECT * FROM table1 AS a LEFT [OUTER] join on a. column = B. column
2) right outer join right [OUTER] JOIN
Displays the data rows that meet the conditions, and the data rows that do not meet the conditions in the data table on the right. No corresponding entries on the left show NULL.
For example
SELECT * FROM table1 AS a RIGHT [OUTER] join on a. column = B. column
3) All external connections
Displays the qualified data rows, and displays the left and right data rows that do not meet the conditions. The corresponding left and right data rows are NULL.