Inner Join vs Outer Join

Here I will show the difference between Inner Joins and Outer Joins hence the title Inner Join vs Outer Join.

I have three tables to use for these examples.

Table1: Customers

CUSTID
FNAME
LNAME
ST_ADDRESS
CITY
STATE
HOME_PHONE
EMAIL

Table 2: Orders

ORDID
CUSTID
PARTID
QUANTITY

Table 3: Parts

PARTID
NAME
DESCRIPTION
QUANTITY

 Inner Join Vs Outer Join

Inner Join

select fname,lname,ordid from customers  
INNER JOIN Orders ON customers.custid = orders.custid;

This query will show customers that have placed orders. Here is a Venn Diagram of the query. This query returns everything within the middle of the diagram. So custid that is in the customers table as well as the orders table. It will not show any customers that have not ordered and will not show any orders that do not have a customer record.

Venn Diagram of Inner Join(Inner Join vs Outer Join)

Venn Diagram of Inner Join

 

 

 

 

 

 

 

The result of this query returns

Result of Inner Join

Result of Inner Join

 

 

 

 

 

 

Left Outer Join

select parts.partid,parts.name,parts.quantity,ORDID from parts
LEFT OUTER JOIN Orders ON parts.partid = orders.partid;

 

This query can be shown as

Left Outer Join Venn Diagram

Left Outer Join Venn Diagram

 

 

 

 

 

 

 

And the result of this query is below

Result of Left Outer Join

Result of Left Outer Join

 

 

 

 

 

 

Here you notice it shows all parts from the parts table whether there has been an order for them or not.

 

Right Outer Join

select parts.partid,parts.name,parts.quantity,ORDID from parts  
RIGHT OUTER JOIN Orders ON parts.partid = orders.partid;

Notice this is the same query as above except I am using Right Outer Join.

Right Outer Join Venn Diagram

Right Outer Join Venn Diagram

 

 

 

 

 

 

 

Here is the result of the query

Right Outer Join Result

Right Outer Join Result

 

 

 

 

 

 

 

Old vs New Inner Join/Outer Join syntax

Ask Tom