Inner and outer joins with SQL examples

Joins can be said to be INNER or OUTER joins, and the two tables involved are referred to as LEFT and RIGHT. By combining these two concepts you get all the various types of joins in join land: Inner, left outer, right outer, and the full outer join.

Tables used for SQL Examples

In the screen shots I've configured Datamartist to only show the name columns to save space. The SQL code shown is "Select *" so it will return all the columns. You can see that in the Datamartist tool the type of join is selected by just checking the parts of the venn diagram that contain the rows you want.

1) Inner Join SQL Example

select * from dbo.Students S INNER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID

2) Left Outer Join SQL Example

select * from dbo.Students S LEFT OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID

3) Full Outer Join SQL Example

select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID

4) SQL example for just getting the rows that don't join

select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID where A.Advisor_ID is null or S.Student_ID is null

5) SQL example for just rows from one table that don't join

select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID where A.Advisor_ID is null