Search code examples
sqlsql-serversql-updatessmscross-join

Why does SQL Management Studio add a cross join?

I sometimes need to run basic updates on a join. For example:

UPDATE t1 SET col1 = 'val1'
FROM table1 as t1
INNER JOIN table2 as t2
ON t1.ID = t2.t1_id
WHERE t2.col3 = 'val3'

This works perfectly, but for some reason, in MS SQL Management Studio Express, it wants to convert this to

UPDATE t1 SET col1 = 'val1'
FROM table1 as t1
INNER JOIN table2 as t2
ON t1.ID = t2.t1_id
CROSS JOIN t2
WHERE t2.col3 = 'val3'

It adds a crossjoin for some reason that I don't understand.

Now my question is: Why does Management Studio think this is what I meant? It must have a genuine use, otherwise it wouldn't suggest it. Yet I have no idea how and when (and why).

Solution

  • According to MSDN a cross join like that is equivalent to an inner join. Perhaps it uses cross joins because a cross join can be used to create cartesian products as well as simpler joins - whereas an inner join is more limited.