[MSSQL, MySQL, Oracle]-join usage

The following code runs in Microsoft SQL Server 2000:

-- The join keyword in SQL statements is a commonly used and incomprehensible keyword. The following example provides a simple explanation (which is executed in the query analyzer below)
-- Delete existing tables and databases
Use master
If exists (select * From sysdatabases where name = 'Study _ join ')
Begin
If exists (select count (*) from sysobjects where id = object_id ('Study _ join. DBO. table1 '))
Begin
Drop table study_join.dbo.table1
End
If exists (select count (*) from sysobjects where id = object_id ('Study _ join. DBO. table2 '))
Begin
Drop table study_join.dbo.table2
End
Drop database study_join;
End

-- Create database study_join:
Create Database study_join;
Go

Use study_join;
-- Create Table Table1, Table2:
Create Table Table1 (ID int, name varchar (10 ));
Insert into Table1 values (1, 'Lee ');
Insert into Table1 values (2, 'zhang ');
Insert into Table1 values (4, 'wang ');

Create Table Table2 (ID int, score INT );
Insert into Table2 values (1, 90 );
Insert into Table2 values (2,100 );
Insert into Table2 values (3,70 );

/*
-- Oracle does not support this syntax, which is supported by MSSQL and MySQL.
Create Table Table1 (ID int, name varchar (10 ));
Insert into Table1 select 1, 'lil ';
Insert into Table1 select 2, 'zhang ';
Insert into Table1 select 4, 'wang ';

Create Table Table2 (ID int, score INT );
Insert into Table2 select 1, 90;
Insert into Table2 select 2,100;
Insert into Table2 select 3,70;
*/

/*
The results are as follows:
Table1 Table2
-------------------------------
ID name | ID score |
-------------------------------
1 Lee | 1 90 |
2 Zhang | 2 100 |
4 Wang | 3 70 |
-------------------------------
*/

--*************************************** *************************************
-- 1. External Connection
-- Left (outer) join (left join is equivalent to left Outer Join)
Select * From Table1 left Outer Join Table2 on table1.id = table2.id;
/*
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.
------------------------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 2 100
4 Wang null
------------------------------
*/

-- Right outer join is equivalent to right outer join)
Select * From Table1 right Outer Join Table2 on table1.id = table2.id;
/*
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.
------------------------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 2 100
Null null 3 70
------------------------------
*/

-- Full outer join is equivalent to full outer join)
-- MySQL does not support this syntax, which is supported by MSSQL and Oracle.
Select * From Table1 full outer join Table2 on table1.id = table2.id;

-- MSSQL, MySQL, and Oracle Support
Select * From Table1 left Outer Join Table2 on table1.id = table2.id
Union
Select * From Table1 right Outer Join Table2 on table1.id = table2.id;

/*
-- Three tables
Select * From Table1 left Outer Join Table2 on table1.id = table2.id left Outer Join table3 on table2.id = table3.id
Union
Select * From Table1 right Outer Join Table2 on table1.id = table2.id left Outer Join table3 on table2.id = table3.id
Union
Select * From Table1 right Outer Join Table2 on table1.id = table2.id right Outer Join table3 on table2.id = table3.id
*/
/*
Note: returns the Union of left and right connections (see upper left and right connections)
------------------------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 2 100
4 Wang null
Null null 3 70
------------------------------
*/

--*************************************** *************************************
-- 2. Internal Connection
-- Inner join (join is equivalent to inner join)
Select * From Table1 inner join Table2 on table1.id = table2.id;
/*
Note: returns the intersection of left and right connections (see upper left and right connections)
------------------------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 2 100
------------------------------
Equivalent (same as the following execution)
Select a. *, B. * From Table1 A, Table2 B where a. ID = B. ID;
Select * From Table1 cross join Table2 where table1.id = table2.id; -- Note: Only the WHERE clause can be used for cross join, not on
*/

--*************************************** *************************************
-- 3. Cross-join (complete)
Select * From Table1 cross join Table2;
/*
Note: 3*3 = 9 records are returned, that is, Cartesian product.
------------------------------
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
------------------------------
*/