You are here

SQL Joins

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 tblA where tblA.id = tblB.Id and tblb.field1='Something'

)

Credits

 

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

External Links

Outer Joins at MSDN

Visual Explanation of Joins

Review: