CODEWITHSUNDEEP
sqloracle-databasejoinsubquery
Ahmadov
Ahmadov

Reputation: 1607

Filtering table with a subquery in the SELECT clause vs filtering with where clause

So, I have the following two queries. In the first one, rcv_transaction table is first filtered by TRANSACTION_TYPE in the select clause, then joined to other tables while in the second query, the tables are first joined then filtered in the WHERE clause. Results are completely different. First query produces almost twice more results than the second query. For me, they should perform the same task. What am I missing here? What is the difference?

1st. Query 

SELECT pl.*
  FROM po_headers_all ph,
       po_lines_all pl,
       (SELECT *
          FROM rcv_transactions r
         WHERE r.TRANSACTION_TYPE = 'DELIVER') rt
 WHERE     ph.PO_HEADER_ID = pl.PO_HEADER_ID
       AND rt.po_header_id(+) = pl.po_header_id
       AND rt.po_line_id(+) = pl.po_line_id

2nd query 

SELECT pl.*
    FROM   po_headers_all ph, 
           po_lines_all pl, 
           rcv_transactions rt
     WHERE     ph.PO_HEADER_ID = pl.PO_HEADER_ID
           AND rt.po_header_id(+) = pl.po_header_id
           AND rt.po_line_id(+) = pl.po_line_id
           AND rt.transaction_type = 'DELIVER'

Upvotes: 2

Views: 2706

Answers (2)

Tomasz Żuk
Tomasz Żuk

Reputation: 1288

If you check the execution plan, you'll see that the 2nd query doesn't perform an outer join, as GarethD has already explained. Add the join operator to the last line to include it as a join condition:

AND rt.transaction_type(+) = 'DELIVER'

Upvotes: 0

GarethD
GarethD

Reputation: 69789

Using ANSI 92 join syntax your queries would be rewritten as:

SELECT  pl.*
FROM    po_headers_all ph, 
        INNER JOIN po_lines_all pl, 
            ON ph.PO_HEADER_ID = pl.PO_HEADER_ID
        LEFT JOIN rcv_transactions rt
            ON rt.po_header_id = pl.po_header_id
            AND rt.po_line_id = pl.po_line_id
            AND rt.transaction_type = 'DELIVER';

and

SELECT  pl.*
FROM    po_headers_all ph, 
        INNER JOIN po_lines_all pl, 
            ON ph.PO_HEADER_ID = pl.PO_HEADER_ID
        LEFT JOIN rcv_transactions rt
            ON rt.po_header_id = pl.po_header_id
            AND rt.po_line_id = pl.po_line_id
WHERE   rt.transaction_type = 'DELIVER';

Since your join to rcv_transactions is a left join when there is no corresponding row in this table the query will return NULL. However you your second query you are stipulating in the WHERE clause that rt.transaction_type = 'DELIVER', therefore any rows where there is no match in rcv_transactions will be excluded because NULL does not equal to DELIVER. This effectively turns your LEFT JOIN into an INNER JOIN. In the first query though this predicate is part of the JOIN you are therefore not affecting the overall results, only the records that are joined.

Upvotes: 2

Related Questions