Inner Join vs Outer Join
The Inner join returns only the rows for which there is an match in both tables.
The Inner join eliminate the rows that do not match with a row from the other table.
The Outer join returns unmatched rows.
The Outer join return all rows from at least one of the tables.
Students table:
| ID | NAME | CITY |
|---|---|---|
| 1 | Emma | New York |
| 2 | Daniel | Chicago |
| 3 | Joseph | Dallas |
| 4 | Jennifer | Los Angeles |
| 5 | Debra | Dallas |
Library table:
| ID | TITLE | STUDENT_ID |
|---|---|---|
| 1 | SQL | 3 |
| 2 | T-SQL | 1 |
| 3 | MSSQL | 5 |
| 4 | PHP | 1 |
| 5 | CSS | 2 |
Inner Join
select s.ID, s.Name, l.Title
from Students s
INNER JOIN Library l
on s.ID=l.Student_id;
Results
| ID | NAME | TITLE |
|---|---|---|
| 1 | Emma | T-SQL |
| 1 | Emma | PHP |
| 2 | Daniel | CSS |
| 3 | Joseph | SQL |
| 5 | Debra | MSSQL |
Full Outer Join
select s.ID, s.Name, l.Title
from Students s
FULL OUTER JOIN Library l
on s.ID=l.Student_id;
Results
| ID | NAME | TITLE |
|---|---|---|
| 1 | Emma | T-SQL |
| 1 | Emma | PHP |
| 2 | Daniel | CSS |
| 3 | Joseph | SQL |
| 4 | Jennifer | NULL |
| 5 | Debra | MSSQL |