How to Find Left Outer Join or Right Outer Join with Oracle Join (+)

find the number of records that left outer join,right outer join and full outer join return

Since you know the total number of records, knowing the number of unmatched records is equivalent to knowing the number of matched records. (Perhaps!)

The answer to your question is NO, you can't determine the number of records in different types of joins by only knowing the cardinalities of the base tables, without knowing how many records are matched (or unmatched). Simple mental exercise: both tables have 100 records. If all are perfectly matched, one-to-one, then all joins are the same as the inner join, and they all have 100 records. If there are no matches at all, the inner join has zero rows, the one-sided joins have 100 rows and the full outer join has 200 rows. And the only difference between these cases is the number of matched (or unmatched) records, there is absolutely nothing else that you might know that will allow you to get the answer without this piece of information.

ADDED after the OP asked a follow-up question:

In fact knowing "how many records are matched" is not well defined, and insufficient anyway. Suppose ALL records from both tables are matched. At one extreme, the matches may be in pairs: there is an id column in both tables, and the values in both tables are all the possible values from 1 to 100. Then the result has 100 rows. On the other hand, suppose the "id" is not unique in either table. Instead, it has the value 1 IN ALL 100 ROWS IN BOTH TABLES. Then every row in the first table matches every row in the second table, and the result set will have 100 x 100 = 10,000 rows.

This is just to suggest the following: "how many rows are matched" is not a well-defined concept. To have a count of the resulting joins (of different kinds), one needs to know what the joins are on, and for each tuple in the join condition, how many rows have that specific tuple in each table. Then the number of rows in the result set of an inner join is a sum of products of such tuple-grouped counts, and additional rows for unmatched rows from the left (or right, or both) table(s) for outer joins.

Oracle using (+) for left outer join

Need to include the (+) Operator on the Condition with the Literal

The older outer join approach requires that you include the (+) operator for the condition on the literal.

Just put your "retro" mental cap on and return back to the 90's. :)

Here is how it should look with this approach:

SELECT
    c
.DATE,
 u.user_id
FROM
    order_detail_trans c,
    order_detail_trans u
WHERE
    u.trans_id (+) = c.trans_id
    AND   u.trans_type (+) = 'UPDATE DETAIL'

Here is an example with the tables, emp and dept:

SCOTT@db>SELECT
  2      d.deptno,
  3      e.job
  4  FROM
  5      dept d
  6      LEFT OUTER JOIN emp e ON d.deptno = e.deptno
  7      and e.job = 'CLERK'
  8  GROUP BY
  9      d.deptno,
 10      e.job
 11  ORDER BY
 12      1,
 13      2;
  DEPTNO JOB     
      10 CLERK   
      20 CLERK   
      30 CLERK   
      40         

SCOTT@db>SELECT
  2      d.deptno,
  3      e.job
  4  FROM
  5      dept d,
  6      emp e
  7  WHERE
  8      d.deptno = e.deptno (+)
  9   AND e.job (+) = 'CLERK'
 10  GROUP BY
 11      d.deptno,
 12      e.job;
  DEPTNO JOB     
      20 CLERK   
      30 CLERK   
      10 CLERK   
      40    

Believe it or not, I believe most Oracle Applications shops just use this older outer join approach.

Multiple LEFT OUTER JOIN on multiple tables

The join on D is an inner join, the rest are left outer joins:

SELECT *
FROM TABLEA A JOIN
     TABLED D
     ON D.Z = A.Z LEFT JOIN
     TABLEB B
     ON A.X = B.X LEFT JOIN
     TABLEC C
     ON B.Y = C.Y 
WHERE MY_COL = @col_val;

I always start chains of joins with inner joins followed by the left outer join. I never use right join, and full join rather rarely. The inner joins define the rows in the result set, so they come first.

Left outer join and necessary subquery with date in Oracle

As MT0 suggested using partitioning and sorting by row number helped. Only had to include value_code in the partitioning for my purpose.

So this query finally did what I wanted:

SELECT *
FROM   CONTRACTS C
       LEFT JOIN
       ( SELECT *
         FROM   (
           SELECT V.*,
                  ROW_NUMBER() OVER ( PARTITION BY CUSTOMER_ID, VALUE_CODE ORDER BY BEGIN_DATE DESC )
                    AS rn
           FROM   VALUES V
         )
         WHERE rn = 1
       ) V
ON     ( C.CUSTOMER_ID = V.CUSTOMER_ID
         AND VALUE_CODE = 'VOLUME' )
WHERE C.STATUS = 'ACTIVE'

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 

---

Related Topics