Thursday, June 27, 2013

SQL Joins Part 3

I was doing some research on the CROSS join since I haven't put this join into practice.  I'm still having a hard time imagining when this would be useful.

For purposes of completeness, I will explain how a CROSS join works.

Unlike other joins, a CROSS join does not have an "on" statement where you provide columns for comparisons.  You can insert comparison columns into the where clause, but when this occurs, you are basically running an inner join.

Following is an example of CROSS join, using two tables that are not related in any way.

FROM Customers

If our Customers table has ten rows and our Vendors table has ten rows, this query will return 100 rows.  Each row from the first table will show with each row in the second table.

Following are two tables that do have comparison columns, with which we will run a CROSS join:

FROM Customers
WHERE Customers.CustomerNumber = Orders.CustomerNumber

We've essentially just run an inner join.

To read more on Cross Joins, go here: Using Cross Joins

What situations do you think a CROSS join could be useful?

No comments:

Post a Comment