Note: The word Outer is optional
| Join Type | Description | Example Code |
|---|---|---|
| Inner Join |
Rows that are in both tables (by id) |
select * from tblA inner join tblB on tblA.id=tblB.id |
| Full [Outer] Join | All rows from both tables even if there isn’t a match |
select * from tblA full outer join tblB on tblA.id=tblB.id |
| Left [Outer] Join (non-matching rows) | Rows from both tables but only where there isn’t a match |
select * from tblA left outer join tblB on tblA.id=tblB.id where tblA.id is null or tblB.id is null |
| Left [Outer] jpin (non-matching rows on additional criteria) | Rows in table A that do not have matching rows in table B based on criteria |
select * from tblA where not exists ( select * from tblB where tblA.id = tblB.Id and tblb.field1=’Something’ )
|
|
Left [Outer] Join | All rows from table A even if there isn’t a matching row in table B |
select * from tblA left outer join tblB on tblA.id=tblB.id |
| Left [Outer] Join (non-matching rows) | Rows in table A that do not match a row in table B |
select * from tblA left outer join tblB on tblA.id=tblB.id where tblB.id is null |
| Right [Outer] Join | All rows from table B even if there isn’t a matching tow in table A |
select * from tblA right outer join tblB on tblA.id=tblB.id |