SQL Joins Part 2

Yesterday we spoke about OUTER joins.  Today we will talk about INNER joins.  INNER joins will return all rows from both tables, as long as the values in the columns you are joining on match.  This differs from OUTER joins in the fact that OUTER joins could in fact return NULL values for one table.  INNER joins only return those values that match within both tables.

Going back to our example of yesterday, we will use the Customers and Orders tables as examples.  First, lets look at an OUTER join:

SELECT *
FROM Customers
LEFT JOIN Orders on Customers.CustomerNumber = Orders.CustomerNumber

The above would return all Customers and any matching orders in the Orders table, even if no orders exist.  Next, we will use the INNER join, as follows:

SELECT *
FROM Customers
INNER JOIN Orders on Customers.CustomerNumber = Orders.CustomerNumber

This will only return data where it exists in both tables.  If a customer doesn't have any orders, no data will be returned for that customer.

As part of your column matching, you can also use math operators ( >, <, <>).  For example, if we want only orders that are over a certain amount, you would run a query like so:

SELECT *
FROM Customers
INNER JOIN Orders on Customers.CustomerNumber = Orders.CustomerNumber and Orders.Amount > 10.00

This will return only those orders where the amount is greater than $10.

To review, OUTER joins will return data for both tables, even if data doesn't exist (it will return NULLs for non-existent data).  INNER joins, on the other hand, will only return data that exists, based upon your matching columns.

To read more on INNER joins, go here: Using Inner Joins