More on Joins.

Here are the whiteboard drawings we did in class. The first is for a CROSS JOIN. in a CROSS JOIN, each row in the first table is matched to every row in the second table. This gives you a result set that shows every possible combination of values. A CROSS JOIN ignores any actual relationships between the tables, that is why they don't have an ON clause to specify the relationship. A CROSS JOIN can be useful for some things, but it is very rare that you will want to use one. In fact, almost never.

An INNER JOIN returns only matching rows, that is only rows where the primary key of the first table is a foreign key in the second table. Any unmatched records are ignored. This is the kind of join you will use at least 90% of the time. It allows you to see all the sales belonging to a particular customer, for instance, or all the people who have donated money.

An OUTER JOIN returns all the records from one side of the relationship and only matching records from the other side. In a LEFT OUTER JOIN the query returns all the records in the first table listed and only matching records in the second. A RIGHT OUTER JOIN reverses that, returning all the records from the second table and only matching records from the first. A FULL OUTER JOIN returns all the records from both tables whether they match or not. OUTER joins are useful for finding records that DON'T match: Customers that don't have orders, Inventory items that no one has ever purchased, etc. Again, only use OUTER JOINS when you are looking for mismatches.