0

I know the definition of inner join, full outer join, left (outer) join, right (outer) join and cross join. But when should we use these? Can anyone name some example?

Don't tell me they are used to in the class.. ; )

Thanks!

sommcewce
  • 73
  • 5
  • 10
  • 1
    When they make sense. And it really will Just Make Sense when dealing with a real situation - the Cardinality of the relations often plays a huge factor. Just keep in mind that they are different operations; you'll see how they work :D – user2246674 Jun 27 '13 at 21:07
  • 2
    First start by reading the answer on http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join – thursdaysgeek Jun 27 '13 at 21:11
  • Well, it is a theoretical question. Why don't you want the other people discuss it here?! – sommcewce Jun 28 '13 at 02:28

1 Answers1

4

See: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Most joins are just managing the various sets of data.

  • Inner join is when I want data that matches in both sets
  • Full Outer join is when I want all data from both sets regardless if they match
  • Left join is all data from one set and only matching data from the other set
  • right join (Same as left but means include all the date from the table to the right of the right join and only the data that matches on the left.
  • Cross join is all data related to all data in both sets generating a full Cartesian product

So when to use them depends on what data you want back.

When would you ever want to use a cross join? Say I have a color table, size table and I want to sell all colors of all sizes for "shirts" a cross join between color and size would give me all sizes in all colors for all shirts. I could then use this to list all the combinations.

An inner join would be I want to show all shirts that are blue so I'd join shirts to color where color is blue. This would eliminate all other shirts.

A left join could be I've have sales of shirts in the various colors and sizes. but I may not have sold an XXL Red shirt yet. But I still want to see it in my results So I left join the size color cross joined to my sales table to ensure I get the Red XXL shirt returned; otherwise as an inner join we'd lose that combination as we've not had any sales.

a Right join is identical to the left it just manages the ORDER in which the tables are joined.

A full outer could be I want to see All customers and all states where we shipped product for shirts. But maybe a customer has only bought pants from us and we've not sold any shirts in Alaska yet. So using a full outer join we can see All customers even if they haven't bought a shirt, and all states even if no shirts were sold there. an Inner would eliminate records as would a left or right join. and a cross join would give us incorrect results as not all states have had sales.

xQbert
  • 34,733
  • 2
  • 41
  • 62