Assume 2 tablesA and B has values with the 1stcolumn 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 |