CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110163

SQL JOIN with OR

Is it possible to do the following in SQL?

SELECT * FROM table1 INNER JOIN table2 ON (col1=col2 OR TRIM(col1)=TRIM(col2))

Or:

SELECT * FROM table1 INNER JOIN table2 USING (col1 OR col2)

Upvotes: 0

Views: 128

Answers (1)

Xophmeister
Xophmeister

Reputation: 9211

You can use arbitrary logic in a join. So, to answer your question: Yes, you can use a disjunction ("OR") in a join. That said, in your specific example, your logic is a tautology -- it's always true -- so it won't achieve anything redundant.

You don't have to join on equality, either. For example, we can mimic windowing functions by comparing magnitude:

select    greatest.id,
          greatest.val
from      someTable greatest
left join someTable greater
on        greater.id  = greatest.id
and       greater.val > greatest.val
where     greater.id is null;

While my above example is pretty common, I've found it very rare to use non-conjunctive joins, per your question. However, it can certainly be done.

Upvotes: 3

Related Questions