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.
Table 2: Orders
Table 3: Parts
Inner Join Vs Outer 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.
The result of this query returns
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
And the result of this query is below
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.
Here is the result of the query
Old vs New Inner Join/Outer Join syntax