Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

OUTER JOIN decreases number of returned rows

Dupa MarynaFeb 9 2024 — edited Feb 9 2024

I am working on 11g EE, and use sql for almost 20 years. Never seen such situation which I am only able to explain as a DB error.

Maybe someone has seen such a phenomenon or can tell me what could be the cause.

I dont have WHERE in last query at all, so it is not the most typical reason.

And it is not even behaves like inner join it shows much less records than JOIN should return.

Any ideas?

WITH 
    jira_users as ( 
        ...
    )
    ,dash (
        id,
        per_id,
        ...
    )
    ,is_n as (
        SELECT  id, MIN(period_id) as per_id
        FROM dash
        GROUP BY id )
SELECT *
FROM dash
    LEFT JOIN is_n ON (is_n.id = dash.id
                    AND is_n.per_id = dash.per_id);
Comments
Post Details
Added on Feb 9 2024
2 comments
112 views