When i run the cross join on huge tables, then obviously it takes time, But when i put this into UNION operation with one more query, which is returning less data, then this entire query runs too fast. How it is happening internally? What i know about UNION is, it searches for distinct data and in my case it returns the less data. For example, I ran one test cross join query and returned 50,00 rows and took 4 seconds, but when i put this query in UNION with one query (which returns 150 rows), then it takes less than a second and just 435 rows. I am just curious to know what happened internally. Thanks in advance.
What is the point of the cross join query? Query returns less than 1% of the cartesian product when the distinct operator (UNION) is added.
[quote user="Adriaan"] What is the point of the cross join query? Query returns less than 1% of the cartesian product when the distinct operator (UNION) is added. [/quote] I agree there is no point in putting cross join query. I was just wondering, if run the cross join query alone it takes 5 min but when i add this query in UNION with one other non cross join query then it takes less than a second. UNION operator searches for DISTINCT values and returns few rows while putting with less data query. Now what i am assuming is when you UNION a cross join query and non cross join query then sql server does not perform cross join operation. I may be wrong here and experts, please correct me.