How to Use Oracle Outer Join with a Filter Where Clause

How to use oracle outer join with a filter where clause

You're much better off using the ANSI syntax

SELECT *
  FROM a
       LEFT OUTER JOIN b ON( a.id = b.id and
                             b.val = 'test' )

You can do the same thing using Oracle's syntax as well but it gets a bit hinkey

SELECT *
  FROM a, 
       b
 WHERE a.id = b.id(+)
   AND b.val(+) = 'test'

Note that in both cases, I'm ignoring the c table since you don't specify a join condition. And I'm assuming that you don't really want to join A to B and then generate a Cartesian product with C.

Oracle - Left outer join with where clause

Your explanation does not correlate with your query. You have mentioned

"However I also need to include a where clause but.... I still want a row from the left-hand table to be returned for each record in the left-hand table even if the condition in the where clause isn't met."

So I believe your query looks something like this

SELECT a.*, 
       b.* 
FROM   a 
       LEFT OUTER JOIN b 
                    ON a.vin = b.vin 
WHERE  Trunc(a.rep_open_date) BETWEEN Trunc(b.check_in_date) + 1 AND 
                                      Trunc(b.check_in_date) - 1 

In the above the LEFT OUTER JOIN will be converted into INNER JOIN due to the filtration of right table in Where clause

So as you have used in first query the right table filters should be part of JOIN condition, Which will return rows from LEFT table even though there is no matching records in RIGHT side table.

SELECT a.*, 
       b.* 
FROM   a 
       left outer join b 
                    ON a.vin = b.vin 
                       AND Trunc(a.rep_open_date) BETWEEN 
                           Trunc(b.check_in_date) + 1 AND 
                           Trunc(b.check_in_date) - 1 

Update :

You have used between operator like 10 between 11 and 9 but it should be 10 between 9 and 11

SELECT a.*, 
       b.* 
FROM   a 
       left outer join b 
                    ON a.vin = b.vin 
                       AND CAST(a.rep_open_date as date) BETWEEN 
                           CAST(b.check_in_date as date) - 1 AND 
                           CAST(b.check_in_date as date) + 1 

Oracle outer join with filter condition on the second table

No, there is no condition under which the result sets will be different.

But your assumption "a.id = b.id(+) has no meaning" is not 100% correct. It has a meaning, because it defines the join, otherwise this would be a cartesian product of a and b with all rows from a and b.name = 'XYZ'.

What has no effect is the (+), because the statement is "semantically" wrong. It makes no sense to outer join on id but to join on name.

Usually something like that is wanted:

select  * from a,b where a.id =b.id(+) and b.name(+) = 'XYZ';

Short example at http://www.sqlfiddle.com/#!4/d19b4/15

SQL Left Outer Join not returning all rows from left table (no where clause filter)

Your outer join works fine.

You probably mean partitioned outer join.

See the additional query_partition_clause in the join

PARTITION BY (sender) only this join will fill the gaps in sender as you expects.

select t1.ov_product
      ,t2.sender
      ,t2.items
  from t1
  left outer join t2
  PARTITION BY  (sender)
    on t1.product = t2.product   
 order by 2, 1

OV_PRODUCT         SE      ITEMS
------------------ -- ----------
Product 1          AT        100
Product 2          AT         25
Product 3          AT           
Product 4          AT         57
Product 1          GR         45
Product 2          GR         22
Product 3          GR          5
Product 4          GR          4

SQL filter LEFT TABLE before left join

A left join follows a simple rule. It keeps all the rows in the first table. The values of columns depend on the on clause. If there is no match, then the corresponding table's columns are NULL -- whether the first or second table.

So, for this query:

select *
from tableA A left join
     tableB B
     on A.ID = B.ID and A.ID = 20;

All the rows in A are in the result set, regardless of whether or not there is a match. When the id is not 20, then the rows and columns are still taken from A. However, the condition is false so the columns in B are NULL. This is a simple rule. It does not depend on whether the conditions are on the first table or the second table.

For this query:

select *
from tableA A left join
     tableB B
     on A.ID = B.ID 
where A.ID = 20;

The from clause keeps all the rows in A. But then the where clause has its effect. And it filters the rows so on only id 20s are in the result set.

When using a left join:

• Filter conditions on the first table go in the where clause.
• Filter conditions on subsequent tables go in the on clause.

Oracle full outer join with filter (ANSI) not working as expected

As others have explained in comments, this is because of where you are putting the filter logic. So your original query will return all muppets and all phone numbers and show them as joined when the IDs match AND valid = 'Y'. So this is why you are seeing all phone numbers, but only matches for valid ones.

You can either do the approach you already figured out, or move the "valid" logic into the WHERE clause:

SELECT   m.id muppet_id,
         m.name,
         p.id phone_id,
         p.phone,
         p.valid
FROM      muppet m
         FULL OUTER JOIN
            phone p
         ON (M.ID = P.MUPPET_ID)
WHERE
         P.VALID = 'Y' or P.MUPPET_ID is null;

Now the where clause is discarding rows where VALID <> 'Y' or where there was no match for the phone table. If you made the where clause only "P.VALID = 'Y'", then you would be effectively turning this into a right outer join.

That said, I would actually pick the second version you listed, as this will allow you to use an index on VALID = 'Y' if that index exists, where the above version will likely not use the index due to the OR in the where clause.

Adding filter on the right side table on Left outer joins

All rows will be returned from your left table regardless. In the case of a left join, if the filter isn't met, all data returned from the right table will show up as null. In your case, all students will show up in your results. If the student doesn't have an instructor, i.name will be null.

Since you are only selecting a column from your left table, your join is pretty useless. I would also add i.name to your select, so you can see the results

In the case of an inner join, rows will only be returned if the join filter is met.

---

Related Topics