What is the difference between inner join and outer join?
June 12th, 2008 | by Shubhabrata |Inner join displays rows from table where the data is available in both the tables, where in outer join we can configure it to bring out rows from one table where the data is missing in other table for the corresponding rows.
Inner join
Say you have one table of CUSTOMERS and one table of ORDERS. Each row in the ORDERS table has a reference (foreign key reference) to a customer id which represents what customer placed that order. If you want to run a query that lists the orders along with the names of the customers who ordered them (since a customer id number itself it pretty useless), you will want to execute a join query:
SELECT CUSTOMERS.NAME, ORDERS.NAME
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
Outer join
If for some reason, you wanted the query results to return all customer name regardless of whether they placed an order, you can use one of two types of OUTER JOINS, in this case, a LEFT JOIN:
SELECT CUSTOMERS.NAME, ORDERS.NAME
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID