Is introducing a where condition into the join clause bad practice

Question

In the query below, the condition

and r.master_seq=206

is used on the on clause as a join condition. In the programme code, the value for r.master_seq is set by a parameter.

My concern is that I'm not sure if it's ok to mix in this condition(r=.master_seq=123, that I believe should be a where condition) into the join condition.
I was thinking that I should only use relational information(primary keys and foreign keys) in the join clause, not a where condition ( where id=123, balance>300, etc).

SELECT
  *
FROM
TB_REQUEST_DATA D

left outer join
TB_SPOT_REPORT_REQUEST r
on D.SEQ = r.REQUEST_SEQ  
and r.master_seq=206

WHERE
  d.SEQ IN (
  7,12,5,11,4
  )


desc tb_request_data;
Name          Null     Type          
------------- -------- ------------- 
SEQ           NOT NULL NUMBER        
REQUEST_NM    NOT NULL VARCHAR2(50)  
REG_DT        NOT NULL DATE          
REG_ID        NOT NULL VARCHAR2(20)  
UP_DT         NOT NULL DATE          
UP_ID         NOT NULL VARCHAR2(20)  
DISASTER_TYPE          VARCHAR2(500) 

desc TB_SPOT_REPORT_REQUEST;
Name         Null     Type          
------------ -------- ------------- 
MASTER_SEQ   NOT NULL NUMBER        
SEQ          NOT NULL NUMBER        
REQUEST_SEQ  NOT NULL NUMBER        
REAL_FILE_NM          VARCHAR2(100) 
DOWN_FILE_NM          VARCHAR2(100) 

Answer 1

Putting a condition in the WHERE clause and the ON clause are two very different things. The ON clause is part of the JOIN. Now with an INNER JOIN you won't see any difference in the output (unless also using GROUP BY ALL). But an OUTER JOIN is a different story. If you moved that particular condition into the WHERE clause it would (among other things), in effect, negate the OUTER portion of your query.

I wrote an article with several examples here.

Just remember that the ON clause joins the two tables together. The WHERE clause excludes rows from the output. For your specific example lets say you have a row in TB_REQUEST_DATA without a match in TB_SPOT_REPORT_REQUEST. With r.master_seq=206 in the ON clause of the OUTER JOIN you will get a row returned with the value of TB_REQUEST_DATA but NULLs in the TB_SPOT_REPORT_REQUEST columns. If on the other hand you put it in the WHERE clause it's going to eliminate the row entirely.

Let's say however that you want every row of TB_REQUEST_DATA but only the information in TB_SPOT_REPORT_REQUEST where master_seq = 206 AND SEQ = REQUEST_SEQ. That's when you would put the condition in to the ON clause.