July 23, 2007 at 3:44 pm
Can somebody explain to me when would this be used?
is this the same as joining to a table not by primary key?
July 23, 2007 at 4:43 pm
DECLARE
@a TABLE (Col1 INT)
INSERT
SELECT
1 UNION ALL
SELECT
2 UNION ALL
SELECT
3 UNION ALL
SELECT
4 UNION ALL
SELECT
5
DECLARE
@b-2 TABLE (Col2 INT)
INSERT
SELECT
1 UNION ALL
SELECT
2
SELECT
*
FROM
CROSS
JOIN @b-2
ORDER
BY Col1,
Col2
SELECT
*
FROM
INNER
JOIN @b-2 ON Col1 = Col2
ORDER
BY Col1,
Col2
SELECT
*
FROM
LEFT
JOIN @b-2 ON Col1 = Col2
ORDER
BY Col1,
Col2
N 56°04'39.16"
E 12°55'05.25"
July 24, 2007 at 2:09 am
You can use a cross to to "explode" your data.
Consider table A and Table B. A has 10 rows, B has 3.
When cross joining them together, your result will be 30 rows. Each row in A will be duplicated for each row in B.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 24, 2007 at 2:07 pm
A cross join is used when you need to join without a join condition. One case would be when you have a table with a single row.
Another case would be when you want to have a cross product of two queries. The function on the link below uses this to generate a number table.
Number Table Function
July 25, 2007 at 12:47 am
This link demonstrates the usage of the Cross Join.
http://blog.sqlauthority.com/2007/05/15/sql-server-binary-sequence-generator-truth-table-generator/
Pinal Dave
sqlauthority.com
July 25, 2007 at 6:11 am
Here's an example. TableTime has two columns, TimeID and a datetime column. It is updated every month with the most recent month-end date (for accounting). You want to pull all your information from the Products table )which doesn't have any TimeID column), but you also want pull the Max or Min monthend date. You cross join Products to Time to get the information in your Select statement.
But why bother? CrossJoins are messy, kill execution plans and 99.9% of the time are not something you want to use. I could populate a variable with my MonthEnd date and then call the variable in the Select statement I'm using to call my Products table info. Much easier, performance is much better and I don't get stuck with repeating rows.
I don't recommend using a CrossJoin unless you absolutely, positively cannot find another way to do your query. And beware using the query designer in Enterprise Manager / SSMS. Sometimes that stupid thing will add a CrossJoin even if you don't want it to. Usually when you have multiple tables with columns of the same name..
July 25, 2007 at 9:31 am
Cross joins have their place, and are not something that you should avoid. Like anything else, they should be used when and only when they are needed. The same is true for inner joins and outer joins.
July 25, 2007 at 9:45 am
I don't know, Michael. I've never seen a situation in any of the jobs where I've worked when a Cross join was needed. Usually, in fact, people are adding them in where they aren't needed and then wondering why their result sets are so badly skewed.
July 25, 2007 at 9:48 am
i dont mean to sound rude or anything but are you a girl brandie?
July 25, 2007 at 10:12 am
I gave two examples where a cross join could be used in my first post.
It is common to have a table with configuration or default information with a single row. To add this to a query with other tables, a cross join is the best way, since there would usually be no column to join on.
In the link I supplied before, it is used to generate a number table where no table exists and without a loop. If you look, you will see the function contains 5 cross joins.
July 26, 2007 at 4:15 am
Actually, your code is just sample code. It doesn't include a description of what you're doing with the code. And there is no real life example included with it, which is why I ignored it.
And I still think there are better ways to do this than your above example but I'm not going to continue to argue about it. Each DBA has their own way of doing things. My personal preference is to avoid Cross Joins as they usually cause more problems for me than they resolve.
July 26, 2007 at 7:05 am
Here's one I problem I've solved with cross joins :
From a template containing a start date and end date. Create all the work tracking reports for each technician that can be assign the work (we never know who in advance, so we must assume that any one of them may be assigned any work).
Template table inner join Calendar (get the weeks between...) CROSS JOIN techs where someFlag = 1.
Any better solution (performance wise, as the results are always correct on this one).
July 26, 2007 at 7:20 am
The link I supplied is production code in use at my company and I believe a number of others. If you want some more examples of how it is used, you can use GOOGLE to search for the function name, F_TABLE_NUMBER_RANGE. That will return about 100 links showing it being used.
You say you think there is a better ways to do it, but I notice that you did not show an example of a better way.
I can understand why you don't want to continue to discuss this topic, since you really don't have a reason for what you are saying, other than you don't like it, or some people manage to get themselves in trouble with cross joins. If someone doesn't know how to use a cross join correctly doesn't mean that there is something wrong with cross joins; it only means that person doesn't know what they are doing.
I didn't say a cross join should be used in every situation, only that it does have its place.
July 26, 2007 at 8:04 am
Actually, in my original post, I did provide a better way to do it. And if a variable doesn't work for you, there's always a WHILE LOOP or a UNION or Temp Tables...
July 26, 2007 at 9:22 am
You said "I still think there are better ways to do this than your above example". You didn't provide the example of something that would duplicate the function of F_TABLE_NUMBER_RANGE from my example without a cross join that was somehow better, and I doubt if you can.
All you did in you original post was describe a situation where a cross join wasn't called for to try to prove they are never needed. Just because a cross join is not called for is one particular situation does not mean they should never be used.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply