sql help - LEFT JOIN, want to include rows that do not have a match

When you use IN, you're telling SQL to take a value and compare it against every value or set of values in a list using =. If any NULL values exist, a row will not be returned--even if both values are NULL.

Source: http://www.techrepublic.com/article/oracle-tip-understand-how-nulls-affect-in-and-exists/#.

Just remove NULL from IN

Hi Gentlemen,

thx for writing....

I am coming from a Microsoft Access background and I am used to coding LEFT OUTER JOIN.

Truthfully, i never really thought about that the LEFT OUT JOIN implicitly works hand in hand with the WHERE CLAUSE that it would automatically return the NULL rows.

You see, at work, we have a tool at work to create Queries, that is called Oracle Discoverer that does not use ANSI.

and work purchased a new tool to create reports. To save time, i wanted to copy the SQL from Discoverer to New tool. Also by using similar SQL, I could have both tools opened side by side to test that my new reports in the new tool match Discoverer (sorry am wary of mentioning new tool's name bec i am not a fan.....  :-)   )

Now i could be wrong, but must each table in ANSI be explicitly connected with JOIN statements, and that in traditional Oracle you could join whatever you want.

FROM TABLE A
       INNER JOIN TABLE A  TO TABLE B
                        ON FIELDA = FIELDB

now if i want to work with table c join to table d, and they have no relationship to the above tables how to code

yet in traditional Oracle you can join whatever you want in the WHERE clause

believe me I am an ANSI join fan, but the above was an impression i got from reading Oracle Discoverer sql.

So I was really looking for in this post, this answer, yet i have to go test it....

where t1.col1=t2.col1(+)

While i am more comfortable with ANSI joins, here my mentor in the IT dept uses traditional joins
    and i thought it would be easier if i have questions, or am on vacation or leave, if i try to adapt to her style....

tx, sandra

Sorry, i made a mistake:

i am working with the + like this:

and ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT.ENTITY_UID(+)  

i just wanted to pull one kind of row (the rows with the MA)  and the students who do not have an MA (yet these students could have other kinds of address records like BU, FA etc)

and using traditional Oracle i do not know how to do that

Yet how did traditional Oracle programmer do this in the past before adopting ANSI standards?

tx... sandra

Hi Everyone,

I changed it to ANSI left OUTER JOIN..... still did not get the students who do not have MA, only got the students that
have MA.

@SlightWV - should i do the technique you showed me in a post last week using ROW NUM to see if the MA record exists?  sql from that project is below



select ACADEMIC_STUDY.ACADEMIC_YEAR,
       ACADEMIC_STUDY.ACADEMIC_YEAR_DESC,
       ACADEMIC_STUDY.ACADEMIC_PERIOD,
       ACADEMIC_STUDY.ACADEMIC_PERIOD_DESC,
       ACADEMIC_STUDY.ID,
       ACADEMIC_STUDY.NAME,
     
       ADDRESS_CURRENT.ADDRESS_TYPE,
       

    ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_Uid,
                                             '201330',
                                             'N',
                                             ACADEMIC_STUDY.Program) as Calc_MaxTerm

  from ODSMGR.DMC_V_ACADEMIC_STUDY_WITH_ATTR                  ACADEMIC_STUDY
          LEFT OUTER JOIN ODSMGR.ADDRESS_CURRENT                    ADDRESS_CURRENT
                 ON   ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT.ENTITY_UID
 

 where ACADEMIC_STUDY.ACADEMIC_PERIOD =
       ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_Uid,
                                             '201330',
                                             'N',
                                             ACADEMIC_STUDY.Program)
                                             
                                                   
   AND ACADEMIC_STUDY.COLLEGE = 'MD'
   AND ACADEMIC_STUDY.STUDENT_STATUS IN ('AS', 'LA')
   
   AND ADDRESS_CURRENT.ADDRESS_TYPE = ('MA')
 
 order by
          ACADEMIC_STUDY.ACADEMIC_PERIOD
         ,ACADEMIC_STUDY.NAME



tx, sandra

The sql that @SlightWV helped me with last week

 SELECT

            ACADEMIC_STUDY.ACADEMIC_YEAR       AS AS_YEAR
          , ACADEMIC_STUDY.COLLEGE             AS AS_COLLEGE
          , ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_UID,:main_DD_AcademicYear.YEAR_CODE ||'30','N', ACADEMIC_STUDY.Program) AS AS_Calc_MaxTerm
          , ACADEMIC_STUDY.STUDENT_CLASSIFICATION AS AS_STUDENT_CLASSIFICATION

          , ACADEMIC_STUDY.ID                  AS AS_ID
          , ACADEMIC_STUDY.name                AS AS_NAME


FROM
          ACADEMIC_STUDY   ACADEMIC_STUDY

, (SELECT DISTINCT STUDENT_COURSEB.ACADEMIC_YEAR
              , STUDENT_COURSEB.ID
              , STUDENT_COURSEB.name
FROM
(
select STUDENT_COURSEA.academic_year
     , STUDENT_COURSEA.COURSE_IDENTIFICATION
     , STUDENT_COURSEA.ID
     , STUDENT_COURSEA.name

     , STUDENT_COURSEA.FINAL_GRADE
     , STUDENT_COURSEA.RN

from (
select
            STUDENT_COURSE.academic_year
          , STUDENT_COURSE.ID
          , STUDENT_COURSE.name
          , STUDENT_COURSE.COURSE_IDENTIFICATION
          , STUDENT_COURSE.FINAL_GRADE
          , row_number() over(partition by STUDENT_COURSE.id
                                         , STUDENT_COURSE.course_identification
                              order by  STUDENT_COURSE.id
                                      , STUDENT_COURSE.course_identification
                                      , STUDENT_COURSE.academic_year ASC
                                      , STUDENT_COURSE.FINAL_GRADE ASC)
                                                                           AS rn
      from STUDENT_COURSE
where (STUDENT_COURSE.COLLEGE = 'MD'
                                  AND
                         STUDENT_COURSE.ACADEMIC_YEAR BETWEEN '2006' AND :main_DD_AcademicYear.YEAR_CODE
                                  AND
                              ( SUBSTR(STUDENT_COURSE.COURSE_IDENTIFICATION,5,1) IN ('1', '2', '3')
                                               OR
                                STUDENT_COURSE.COURSE_IDENTIFICATION = 'MEDI4003'))

--ORDER BY STUDENT_COURSE.COURSE_IDENTIFICATION
  --            ,STUDENT_COURSE.NAME
    --          ,STUDENT_COURSE.ACADEMIC_YEAR desc

) STUDENT_COURSEA

where rn  > 1
AND STUDENT_COURSEA.ACADEMIC_YEAR  = :main_DD_AcademicYear.YEAR_CODE
AND FINAL_GRADE <> 'Y'

--ORDER BY STUDENT_COURSEA.COURSE_IDENTIFICATION
  --       ,STUDENT_COURSEA.NAME

) STUDENT_COURSEB

) STUDENT_COURSEC


WHERE

--      ACADEMIC_STUDY.ID = '200714348'
  --                 AND

      ACADEMIC_STUDY.COLLEGE = 'MD'
                    AND
      ACADEMIC_STUDY.ACADEMIC_YEAR = :main_DD_AcademicYear.YEAR_CODE
                    AND
      ACADEMIC_STUDY.ACADEMIC_PERIOD =  ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_UID,'2012'||'30','N', ACADEMIC_STUDY.Program)

-- JOINING:   STUDENT_COURSEC  TO ACADEMIC_STUDY
                    AND
          (STUDENT_COURSEC.ACADEMIC_YEAR = ACADEMIC_STUDY.ACADEMIC_YEAR (+)
                                        AND
           STUDENT_COURSEC.ID = ACADEMIC_STUDY.ID (+))
                             and
           :BT_MC_RepeatedCourse_Details is not null





ORDER BY
         ACADEMIC_STUDY.STUDENT_CLASSIFICATION
         , STUDENT_COURSEC.NAME

Just like last week:  How about some small test cases instead of EVERYTHING.

I still don't understand the complete SQL you keep posting and I really doubt I ever will.

If you can post a small test case and expected results then we can post working SQL based from that.  You can then take what we post and apply it back to your larger select.

Hi SlightWV,

is this ok?  tx, sandra

Sample data

ACADEMIC_STUDY
1111111 Washington, George    AS
2222222 Lincoln, Abraham         LA

fields:  id, name, student status

ADDRESS_CURRENT
1111111  Washington, George   MA
2222222  LIncoln, Abraham        BU

fields:  id, name, address_type

results set  (join of the tables)

1111111 Washington, George  MA   AS
2222222 Lincoln, Abraham       null   LA

Hi @PortletPaul,

thx for writing....
     yes, work purchased a different reporting tool because Oracle will no longer support the Discoverer BI tool.

     i wanted to compare results  using the == same == SQL with both tools open.

     if i would have the same results in new tool, i know my new tool's query is fine.

     i do have access sql plus, but have no experience. i pasted my sql there, it ran, the rows just zipped on the screen. can i export from sql plus to Excel?


Hi @SlightWV,

I  understand your question, now:

 Asking me that both students are existing in both tables so how could it be an OUTER JOIN
                 since the join is on Student Id.

I mistakenly thought the WHERE ADDRESS_TYPE = 'MA'  would be control/be part of the OUTER JOIN
               that even though YES matching on Id for both students, yet, bec the Lincoln, Abraham does not have a MA record, it would be like there was no match.

So maybe I should pull the MAs into a virtual table, and if I join ACADEMIC_STUDY  to virtual table, Lincoln, Abe will not have a match.

Will go work on it now.... thx, sandra

Hi Gentlemen,

So this SQL works and gives me the results needed....  just wish it would run a little bit faster

    especially, that this is just a piece of a large SQL that used more tables and more joins

From this post , absorbed concepts about:
    = Oracle world is moving toward ANSI style of coding table joins
    = about testing for a value on  WHERE CLAUSE with OUTER JOIN  gives EQUI join results
              so that if want the unmatched rows, to create a virtual table and join against that

thx everyone, sandra

select ACADEMIC_STUDY.ACADEMIC_YEAR,
       ACADEMIC_STUDY.ACADEMIC_YEAR_DESC,
       ACADEMIC_STUDY.ACADEMIC_PERIOD,
       ACADEMIC_STUDY.ACADEMIC_PERIOD_DESC,
       ACADEMIC_STUDY.ID,
       ACADEMIC_STUDY.NAME,
     
       ADDRESS_CURRENT_2.STREET_LINE1,
       ADDRESS_CURRENT_2.STREET_LINE2,
       ADDRESS_CURRENT_2.ADDRESS_TYPE,
       ADDRESS_CURRENT_2.CITY,
       ADDRESS_CURRENT_2.STATE_PROVINCE,
       ADDRESS_CURRENT_2.NATION_DESC,
       ADDRESS_CURRENT_2.PREFERRED_ADDRESS_IND,
       ADDRESS_CURRENT_2.COUNTY_DESC,

       

    ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_Uid,
                                             '201330',
                                             'N',
                                             ACADEMIC_STUDY.Program) as Calc_MaxTerm

  from ODSMGR.DMC_V_ACADEMIC_STUDY_WITH_ATTR     ACADEMIC_STUDY
       
         LEFT OUTER JOIN  
                 ( SELECT
                          ADDRESS_CURRENT.ENTITY_UID,
                          ADDRESS_CURRENT.STREET_LINE1,
                          ADDRESS_CURRENT.STREET_LINE2,
                          ADDRESS_CURRENT.ADDRESS_TYPE,
                          ADDRESS_CURRENT.CITY,
                          ADDRESS_CURRENT.STATE_PROVINCE,
                          ADDRESS_CURRENT.NATION_DESC,
                          ADDRESS_CURRENT.PREFERRED_ADDRESS_IND,
                          ADDRESS_CURRENT.COUNTY_DESC
                   FROM
                       ADDRESS_CURRENT
                   WHERE
                       ADDRESS_TYPE = 'MA'    
                  ) ADDRESS_CURRENT_2
                           ON   ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT_2.ENTITY_UID      
 

 where ACADEMIC_STUDY.ACADEMIC_PERIOD =
       ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_Uid,
                                             '201330',
                                             'N',
                                             ACADEMIC_STUDY.Program)
                                             
                                                   
   AND ACADEMIC_STUDY.COLLEGE = 'MD'
   AND ACADEMIC_STUDY.STUDENT_STATUS IN ('AS', 'LA')
 
 order by
          ACADEMIC_STUDY.ACADEMIC_PERIOD
         ,ACADEMIC_STUDY.NAME

>> so that if want the unmatched rows, to create a virtual table and join against that

Not sure if this is necessary or not.  Again, I do not understand your table structures or what you are trying to achieve.  I've never received a simplified test case that I've been asking for.

Hi SlightWV,

guess i do not understand the definition of "test case".... thought that means "test data"  which typed above that am pasting below to make it easier:

Sample data

ACADEMIC_STUDY
1111111 Washington, George    AS
2222222 Lincoln, Abraham         LA

fields:  id, name, student status

ADDRESS_CURRENT
1111111  Washington, George   MA
2222222  LIncoln, Abraham        BU

fields:  id, name, address_type

results set  (join of the tables) that SQL should provide

1111111 Washington, George  MA   AS
2222222 Lincoln, Abraham       null   LA

fields: id, name, address_type, student_status

Even thought Lincoln, Abraham is indeed in the ADDRESS_CURRENT table, he does not have a MA record he has a BU record, so it's really a mismatch for business purposes, like he does not exist in the ADDRESS_CURRENT table.

So what i learned is to create a virtual sql table just containing the MA records, and join to that, so that Lincoln, Abraham shows but with null in the address_type field, bec he truly is not in the MA subset.

I suppose there are many ways using SQL to create the subset.  I just did the virtual table idea to join to.
I wonder which is the fastest technique to create a subset.... for now, I learned key concepts through this post, so i am happy....

I am going to try the sql plus tips that you provided...

will write back soon... thx, sandra

>>which typed above that am pasting below to make it easier:

Sorry I missed some stuff during this thread.

Now that I've put it all together, I don't think you need the inline view (what you call a virtual table).

For my test case based on your data posted above, see if this works better for you:

--drop TABLE ACADEMIC_STUDY purge;
create TABLE ACADEMIC_STUDY (
	id number,
	name varchar2(30),
	status char(2)
);

--drop TABLE ADDRESS_CURRENT purge;
create TABLE ADDRESS_CURRENT (
	id number,
	name varchar2(30),
	address_type char(2)
);


--ACADEMIC_STUDY
insert into academic_study values(1111111,'Washington, George','AS');
insert into academic_study values(2222222,'Lincoln, Abraham','LA');

--ADDRESS_CURRENT
insert into address_current values(1111111,'Washington, George','MA');
insert into address_current values(2222222,'Lincoln, Abraham','BU');
commit;

select s.id, s.name, c.address_type, s.status 
from academic_study s
	left outer join address_current c
	on s.id=c.id and
		s.name=c.name and
		c.address_type='MA'
/

Select allOpen in new window

Guess I should have posted the original syntax version as well\.

Same tables and setup as above:

select s.id, s.name, c.address_type, s.status 
from academic_study s, address_current c
where s.id=c.id(+) and
	s.name=c.name(+) and
	c.address_type(+)='MA'
/

Select allOpen in new window

tip-a: Slowness of that query probably comes from the function call in the where clause of your query.
tip-b:you may want to ask how to get and use an "explain plan"

Now that you are using ANSI syntax, what you originally wanted is easier to achieve IMHO. The method you have used (a "derived table") is perfectly valid but you could simply use a "join condition"

SELECT
      ACADEMIC_STUDY.ACADEMIC_YEAR
    , ACADEMIC_STUDY.ACADEMIC_YEAR_DESC
    , ACADEMIC_STUDY.ACADEMIC_PERIOD
    , ACADEMIC_STUDY.ACADEMIC_PERIOD_DESC
    , ACADEMIC_STUDY.ID
    , ACADEMIC_STUDY.NAME
    , ADDRESS_CURRENT.STREET_LINE1
    , ADDRESS_CURRENT.STREET_LINE2
    , ADDRESS_CURRENT.ADDRESS_TYPE
    , ADDRESS_CURRENT.CITY
    , ADDRESS_CURRENT.STATE_PROVINCE
    , ADDRESS_CURRENT.NATION_DESC
    , ADDRESS_CURRENT.PREFERRED_ADDRESS_IND
    , ADDRESS_CURRENT.COUNTY_DESC
    , ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_Uid,
      '201330',
      'N',
      ACADEMIC_STUDY.Program) AS Calc_MaxTerm

FROM ODSMGR.DMC_V_ACADEMIC_STUDY_WITH_ATTR ACADEMIC_STUDY

      LEFT OUTER JOIN ADDRESS_CURRENT
            ON ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT.ENTITY_UID

                  AND ADDRESS_CURRENT.ADDRESS_TYPE = 'MA' --<< join condition


WHERE ACADEMIC_STUDY.ACADEMIC_PERIOD =
      ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_Uid,
      '201330',
      'N',
      ACADEMIC_STUDY.Program)  --<<<<<<<<<<<<<< probably cause of slowness

      AND ACADEMIC_STUDY.COLLEGE = 'MD'
      AND ACADEMIC_STUDY.STUDENT_STATUS IN ('AS', 'LA')

ORDER BY
        ACADEMIC_STUDY.ACADEMIC_PERIOD
      , ACADEMIC_STUDY.NAME

Select allOpen in new window

Thx Gentlemen,

sorry for the delayed response....

had to handle other things,

going to dig in to your sql code(s) now....

tx, sandra

Hi PortletPaul and SlightWV,

== OMG ==..... You have both taught me a concept that I never knew....

*** that you are allowed to code a WHERE-like condition in the LEFT join *** !!!!

I thought that the ON clause was simply to link the similar field-keys that the tables shared.

I just test the sql, and it worked, the rows that do not have MA, are being included in the results set, with NULL in the address... which is what i want.....

(I want to try this concept in Microsoft Access to see if they will allow it.....)

G-d bless everyone at EE, everyone on this post, and especially both of you for caring to dig deep to understand....

Now I will go apply this concept to my real SQL which is a little bigger....

****  Question, does this approach run faster than in line views/derived tables?  just wondering....

@PortletPaul, yes the function probably takes time, as it get the MAX(Academic_Period) for a student to return the last term the student was registered in, this way I get one row, and not get cartesian product results....

tx Everyone, sandra

Hi Gentlemen,

am back... to just to crystallize:

@PortletPaul's idea:
     use NASI left join =  allowed to code a condition in the ON statement

@SlightWV's idea:

     allowed to test a field from the left-joined-table ==  in the WHERE CLAUSE  == ( in the traditional way of joining tables
using the plus sign)

tx, sandra

    and ADDRESS_CURRENT.ADDRESS_TYPE (+) = 'MA'

Thank you for your kind words of thanks.  We are very happy to help.

:-)

Thank you for your kind words of thanks.  We are very happy to help.

Yes, thank you, too ;-)

Sandra, it is a great pleasure to assist. Cheers, Paul

btw: I prefer "ye olde" to describe the dark days before ANSI joins :-)

oh, forgot to answer

****  Question, does this approach run faster than in line views/derived tables?  just wondering....

ah! well, erhum, "it depends" I'm afraid is the prudent answer

Now you are using Oracle you have access to "explain plans" which give you insights into how a query is handled internally. If comparing different approaches it is useful to compare the explain plans. This topic however deserves more than a 3 sentence comment.