CODEWITHSUNDEEP
sqldb2
em3ricasforsale
em3ricasforsale

Reputation: 352

DB2 INNER JOIN OR Statement

I am writing a query for one of my applications that I support, and I am looking for what will cost less in trying to get a DB2 version of an OR statement in the join. Since you can't use "OR" inside on a 

Select * 
FROM
  TABLE A INNER JOIN TABLE B
ON 
   A.USERNAME = B.NAME
OR
   A.USERNAME = B.USERNAME

I have been looking at trying UNION or UNION ALL, but I am worried that the match up may take longer to return.

Upvotes: 2

Views: 33620

Answers (3)

Kumawat
Kumawat

Reputation: 1

SELECT GROUP_REDUCTION.TOUR, FROM_DATE, TO_DATE, DISCOUNT, GROUP_SIZE, REDUCTION
FROM SEASON_DISCOUNT
RIGHT JOIN GROUP_REDUCTION ON SEASON_DISCOUNT.TOUR = GROUP_REDUCTION.TOUR
ORDER BY GROUP_REDUCTION.TOUR, FROM_DATE;

Upvotes: 0

Andriy M
Andriy M

Reputation: 77667

While I join others in confirming that OR can easily be used in join conditions, like in any other conditions (and so your query is perfectly fine), you could still do without OR in this particular case.

Conditions like column = value1 OR column = value2, where the same column is tested against a number of various fixed values, could be transformed into column IN (value1, value2). The latter should work no worse than the former and arguably looks clearer in the end.

So:

SELECT *
FROM
  TABLE A
INNER JOIN
  TABLE B
ON 
  A.USERNAME IN (B.NAME, B.USERNAME)

Upvotes: 5

ron tornambe
ron tornambe

Reputation: 10780

You can revert to using the WHERE clause syntax for joining. If the DB2 optimizer is clever (and I suspect it is), it should perform equally as well as JOIN:

SELECT * FROM TABLE1 A, TABLE2 B
WHERE A.USERNAME = B.NAME OR A.USERNAME=B.USERNAME

Upvotes: 1

Related Questions