SQL query comparison (join condition in ON clause or in WHERE clause)

2
votes

SQL fiddle link for sample data

Basically my doubt is , is it same if we specify a condition in ON clause of LEFT OUTER JOIN or we specify condition in WHERE clause with null check ?

Table schema :

Create table App(ID number , STATUS varchar2(10));
Create table App_Child(child_id number ,
                       STATUS varchar2(10),
                       ID number );

Query 1

SELECT a.ID AS appID, a.STATUS AS appSTATUS,
b.child_id AS acOWNID,b.STATUS AS acSTATUS, b.id AS acID
 FROM App  a LEFT OUTER JOIN App_Child b ON (a.id=b.id AND b.STATUS <> 'disabled')  WHERE a.ID = ?;

Query 2 

SELECT a.ID AS appID, a.STATUS AS appSTATUS,
b.child_id AS acOWNID,b.STATUS AS acSTATUS, b.id AS acID
 FROM App  a LEFT OUTER JOIN App_Child b ON (a.id=b.id)  WHERE a.ID = ? AND (b.STATUS IS NULL OR b.STATUS<>'disabled');
1
sqldatabaseoraclejoinleft-join

1 Answers

3
votes

Not not is the same neither in results and readability.

  • When you write condition in on clause you include all rows from App.
  • When you write condition in where clause you filter rows from result:

In your case an App with row related with a App_Child with b.STATUS='disabled' will be filtered

Here a sample:

INSERT INTO App VALUES(1,'active');

INSERT INTO App_Child VALUES(3,'disabled',1);

SELECT a.ID AS appID, a.STATUS AS appSTATUS,
b.child_id AS acOWNID,b.STATUS AS acSTATUS, b.id AS acID
 FROM App  a LEFT OUTER JOIN App_Child b 
      ON (a.id=b.id AND b.STATUS <> 'disabled')  
WHERE a.ID = 1;

--- has results ---

SELECT a.ID AS appID, a.STATUS AS appSTATUS,
b.child_id AS acOWNID,b.STATUS AS acSTATUS, b.id AS acID
 FROM App  a LEFT OUTER JOIN App_Child b ON (a.id=b.id)  
WHERE a.ID = 1 AND (b.STATUS IS NULL OR b.STATUS<>'disabled');

-- don't has results --