A mix join works a cartesian product around the tuples of these two sets.

FROM Table1

Which conditions render this kind of SQL operation particularly helpful?

For those who have a "power grid" that you would like to populate completely, like size and color information for the piece of clothing:

    sizes CROSS JOIN colors

Maybe you'll need a table that consists of a row for each minute within the day, and you need to utilize it to ensure that the procedure has performed for each minute, so you may mix three tables:

    hours CROSS JOIN minutes CROSS JOIN seconds

Or you've got a group of standard report specs that you would like to use to each month around:

    reports CROSS JOIN months

The issue with maintaining these as sights is the fact that generally, you wouldn't want an entire product, particularly regarding clothes. You can include MINUS logic towards the query to get rid of certain combinations you don't carry, but you will probably find it simpler to populate a table another way and never make use of a Cartesian product.

Also, you may finish up using the mix join on tables which have possibly a couple of more rows than you thought, or possibly your WHERE clause was partly or completely missing. For the reason that situation, your DBA will inform you quickly from the omission. Usually she or he might not be agreeable.

You are typically not likely to desire a full Cartesian product for many database queries. The entire energy of relational databases is you can apply whatever limitations you may be thinking about to let you avoid tugging unnecessary rows in the db.

I guess one contrived example where you may want that's for those who have a table of employees along with a table of jobs that require doing and wish to see all possible projects of 1 worker to 1 job.

Ok, this most likely will not answer the question, but, whether it's true (and I am not really certain of that) it is a fun little bit of history.

In the past of Oracle, among the designers recognized he required to duplicate every row in table (it is possible it had been a table of occasions and that he required to change it out "start event" and "finish event"). He recognized when he'd a table with only two rows, he could perform a mix join, choosing only the posts within the first couple of, and obtain exactly had he needed. So he produced an easy table, that they naturally enough known as "DUAL".

Later, he have to do a thing that could simply be done using a choose from the table, despite the fact that the experience itself had nothing related to the table, (possibly he didn't remember his watch and desired to browse the time via Choose SYSDATE FROM...) He recognized the he still had his DUAL table laying around, and used that. Before long, he fed up with seeing time printed two times, so he eventual erased among the rows.

Others at Oracle began using his table, and finally it had been made the decision to incorporate it within the standard Oracle installation.

Which describes why a table whose only significance is it has one row includes a title meaning "two".

Takes something similar to a numbers table, that has ten rows for that numbers -9. You should use mix join on that table a couple of occasions to some get result which has however many rows you'll need, using the results designated properly. It has numerous uses. For instance, you are able to mix it having a datadd() function to obtain a looking for every single day inside a given year.

It is really an interesting way to utilize a mix join to produce a crosstab report. I discovered it in Joe Celko's SQL For Smarties, and also have tried on the extender several occasions. It will take some setup, but continues to be well worth the time invested.

Generate data for testing.