Is Having an 'Or' in an Inner Join Condition a Bad Idea

Is having an 'OR' in an INNER JOIN condition a bad idea?

This kind of JOIN is not optimizable to a HASH JOIN or a MERGE JOIN.

It can be expressed as a concatenation of two resultsets:

SELECT  *
FROM    maintable m
JOIN    othertable o
ON      o.parentId = m.id
UNION
SELECT  *
FROM    maintable m
JOIN    othertable o
ON      o.id = m.parentId

, each of them being an equijoin, however, SQL Server's optimizer is not smart enough to see it in the query you wrote (though they are logically equivalent).

Condition on main table in inner join vs in where clause

In the scope of the question,

Is there any reason on an INNER JOIN to have a condition on the main
table vs in the WHERE clause?

This is a STYLE choice for the INNER JOIN.

---

From a pure style reflection point of view:

While there is no hard and fast rule for STYLE, it is generally observed that this is a less often used style choice. For example that might generally lead to more challenging maintenance such as if someone where to remove the INNER JOIN and all the subsequent ON clause conditions, it would effect the primary table result set, OR make the query more difficult to debug/understand when it is a very complex set of joins.

It might also be noted that this line might be placed on many INNER JOINS further adding to the confusion.

Performance of joins with OR

Join each way separately, then combine the results:

SELECT T1.ProdID, T1.PartNumber, T1.Data, ISNULL(tprodid.DataB, tpartno.DataB) as DataB
FROM Table1 T1
LEFT JOIN Table2 tprodid ON T1.ProdID = tprodid.ProdID
LEFT JOIN Table2 tpartno ON T1.PartNumber = tpartno.PartNumber;

This will use both indexes and will perform well. You may want to tweak the ISNULL logic to your liking.

SQL JOIN - WHERE clause vs. ON clause

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

INNER JOIN ON vs WHERE clause

INNER JOIN is ANSI syntax that you should use.

It is generally considered more readable, especially when you join lots of tables.

It can also be easily replaced with an OUTER JOIN whenever a need arises.

The WHERE syntax is more relational model oriented.

A result of two tables JOINed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.

It's easier to see this with the WHERE syntax.

As for your example, in MySQL (and in SQL generally) these two queries are synonyms.

Also, note that MySQL also has a STRAIGHT_JOIN clause.

Using this clause, you can control the JOIN order: which table is scanned in the outer loop and which one is in the inner loop.

You cannot control this in MySQL using WHERE syntax.

---

Related Topics