cross join & natural join & inner join & left outer join & right outer join & full outer join

Assume 2 tablesA and B has values  with the 1^stcolumn of each table as the common column , in the following:

 

Table A:

2
3

 

Table B:

1	A	I
2	B	II
4	D	IV

 

Concrete Example:

Table Course:

NAME	COURSE NUMBER
John	ITEC 122
Cindy	ITEC 120

 

 

 

Table  Student:

NAME	Phone Number	GPA
John	6391111	2.0
David	8311111	3.0
Cindy	7311111	4.0

 

1.    Cross Join: append at the end of everyrow of Table A by every row of Table B.

SQL command:select * from A cross join B;

Result:

2		1	A	I
2		2	B	II
2		4	D	IV
3		1	A	I
3		2	B	II
3		4	D	IV

 

 

 

2.    Natural Join: after Cross Join, pick uponly rows with the same (or matching) common column values. Show the commoncolumn value only once. Must have common names between 2 tables.

SQL command:select * from A natural join B;

Result:

2

B

II

 

 

 

Concrete Example: SQL: select * from Coursenatural join Student;

 

Result:

NAME	COURSE NUMBER	Phone Number	GPA
John	ITEC 122	6391111	2.0
Cindy	ITEC 120	7311111	4.0

 

3.    Inner Join: after Cross Join, pick uponly rows with the same (or matching) common column values. However, show thecommon matched column values for both tables. You must specify the commoncolumn(s).

SQL command:select * from A inner join B

   OnA.commoncolumn=B.commoncolumn;

Result:

2

2

B

II

 

Concrete Example: SQL: select * from Courseinner join Student

On Course.Name= Student. Name;

 

Result:

NAME	COURSE NUMBER	NAME	Phone Number	GPA
John	ITEC 122	John	6391111	2.0
Cindy	ITEC 120	Cindy	7311111	4.0

 

4.    Outer Join:

 

   (1) Left Outer Join:after Inner Join, add rows of the left Table (A) which is non-matching commoncolumn values from row of Cross Join with all fields of Table B blanked.  You must specify the common column(s).

SQL command:select * from A left outer join B

   OnA.commoncolumn=B.commoncolumn;

Result:

 

2		2	B	II
3

 

Concrete Example: If you want to find outwho are new in the school (who don’t have any GPA yet)

SQL: select * from Course left outer joinStudent

On Course.Name= Student. Name;

 

Result:

NAME	COURSE NUMBER	Phone Number	GPA
John	ITEC 122	6391111	2.0
Cindy	ITEC 120	7311111	4.0

 

 

 

   (2) Right Outer Join:after Inner Join, add rows of the right Table (B) which is  non-matching common column values from row ofCross Join with all fields of Table A blanked. You must specify the common column(s).

SQL command:select * from A right outer join B

   OnA.commoncolumn=B.commoncolumn;

Result:

 

2		2	B	II
		1	A	I
		4	D	IV

 

Concrete Example: If you want to find outstudents who are not taking any class

SQL: select * from Course right outer joinStudent

On Course.Name= Student. Name;

 

Result:

NAME	COURSE NUMBER	Phone Number	GPA
John	ITEC 122	6391111	2.0
David		8311111	3.0
Cindy	ITEC 120	7311111	4.0

 

 

   (3) Full Outer Join:after Inner Join, add rows of the left Table (A) and the right Table (B) whichare  non-matching common column valuesfrom row of Cross Join with all fields of the other Table blanked.  You must specify the common column(s).

SQL command:select * from A full join B

   OnA.commoncolumn=B.commoncolumn;

Result:

 

2		2	B	II
3
		1	A	I
		4	D	IV