How to fetch the non matching rows in Oracle

Question

Can anyone help me fetch the non matching rows from two tables in Oracle?

Table: Names

Class_id        Stud_name
S001          JAMES
S001          PETER
S002          MARK

Table: Course

 Course_id   Stud_name
  S001       JAMES
  S001       KEITH
  S002       MARK

Output

I need the rows to display as

   CLASS ID    STUD_NAME_FROM_NAME_TABLE    STUD_NAME_FROM_COURSE_TABLE
   --------------------------------------------------------------------- 
    S001          PETER                        KEITH

I have used Oracle joins to fetch the non matching names:

      SELECT * 
        FROM Names, Course 
       WHERE Names.Class_id=Course.Course_id 
         AND Names.Stud_name<>Course.Stud_name

This query is returning duplicate rows.

http://stackoverflow.com/a/7971429/2806972 just follow the link. may you get the answer — Kumar Shanmugam, Jan 06 '14 at 11:43
@Rams how do confirm Peter or James is the right one from names table for S001 ? — Maheswaran Ravisankar, Jan 06 '14 at 12:07
It is not clear what you want. That starts with the data model. Why is there both a class and a course, both using the same id? Are the tables supposed to show the same data and you are looking for differences? All pupils in class that are not in the according course? And all pupils in course that are not in the according class? And rather than just listing the wrong students, you want to list the wrong students per class/course id? Is that so? — Thorsten Kettner, Jan 06 '14 at 12:21

Maheswaran Ravisankar · Answer 1 · 2014-01-06T11:58:01.137

1

Fetches unmatched rows in Names table

 SELECT * FROM Names
 WHERE 
 NOT EXISTS
 (SELECT 'x' from Course 
  WHERE 
  Names.Class_id = Course.Course_id AND 
  Names.Stud_name = Course.Stud_name)

Fetches unmatched rows in Names and Course too!

 SELECT Names.Class_id,Names.Stud_name,C1.Stud_name
 FROM Names , Course C1
 WHERE Names.Class_id = C1.Course_id AND
 NOT EXISTS
 (SELECT 'x' from Course C2
  WHERE 
  Names.Class_id = C2.Course_id AND 
  Names.Stud_name = C2.Stud_name);

edited Jan 06 '14 at 11:58

answered Jan 06 '14 at 11:47

Maheswaran Ravisankar

17,652
6
47
69

Hi , the above query returns the duplicated rows, Its not giving the exact result as my requirement – Rams Jan 08 '14 at 02:00

score 1 · Answer 2 · answered Jan 06 '14 at 11:50

1

If you insist on Join you can use this one:

SELECT * 
FROM Names
   FULL OUTER JOIN Course ON Names.Class_id=Course.Course_id 
      AND Names.Stud_name = Course.Stud_name
WHERE Names.Stud_name IS NULL or Course.Stud_name IS NULL

answered Jan 06 '14 at 11:50

Wernfried Domscheit

54,457
9
76
110

René Nyffenegger · Answer 3 · 2014-01-06T11:48:47.877

When you ask for unmatching rows I assume that you want rows that exist in names but not in course.

If this is the case you're probably after

select * from names 
 where (class_id, stud_name ) not in 
   (select course_id, stud_name from course);

Your query returned duplicate rows beacuse for each row in names it selected all rows in course that satisfied the where condition.

So, for the row S001, PETER in names it faound that S001, JAMES and S001, KEITH matched that condition, thus, that row was "returned" twice.

EDIT Since it is not clear if stud_name is a primary key, or unique (and on second sight I think it's not), you'd probably want a

select * from names 
 where not exists (
   select 1 from course where
      names.class_id  =  course.course_id and
      names.stud_name <> course.stud_name
 )

Edit II if you insist on using a join (as per your comment) you might want to try a

select distinct names.* from...

Thanks, Instead of this i am trying using Joins, Is it possible to achieve any join techniques rather than IN Operator — Rams, Jan 06 '14 at 11:45

Thorsten Kettner · Answer 4 · 2014-01-07T07:42:22.803

Well, I am not sure if I understand correctly what you are asking. I think you want a list of all IDs where the student list in class table and course table differs. Then you want to show the id and the students that are in class but not in course and the students that are in course but not in class.

To do so you would full outer join the tables. That gives you students that are both in class and course, students that are in class and not in course, and students that are in course and not in class. Filter your results where either class_id or course_id is null then to get the students missing in course or class. At last group by id and list the students.

select coalesce(class.class_id, course.course_id) as id
  , listagg(class.stud_name, ',') within group (order by class.stud_name) as missing_in_course
  , listagg(course.stud_name, ',') within group (order by course.stud_name) as missing_in_class
from class 
full outer join course 
on (class.class_id = course.course_id and class.stud_name = course.stud_name)
where class.class_id is null or course.course_id is null
group by coalesce(class.class_id, course.course_id);

Here is the SQL fiddle showing how it works: http://sqlfiddle.com/#!4/8aaaa/2

EDIT: In Oracle 9i there is no listagg. You can use the inofficial function wm_concat instead:

select coalesce(class.class_id, course.course_id) as id
  , wm_concat(class.stud_name) as missing_in_course
  , wm_concat(course.stud_name) as missing_in_class
from class 
full outer join course 
on (class.class_id = course.course_id and class.stud_name = course.stud_name)
where class.class_id is null or course.course_id is null
group by coalesce(class.class_id, course.course_id);

Hi i am using oracle 9i, So can you provide a solution in that, thanks — Rams, Jan 07 '14 at 02:01
In Ora9i there is no official string aggregation function. However there is the undocumented wm_concat you can use. (Else you would have to write a string aggregate yourself.) I've edited my answer. — Thorsten Kettner, Jan 07 '14 at 07:45
Seems Wm Concat also not supporting in Oracle 9i, is there any alternatives for that — Rams, Jan 07 '14 at 09:22
Sorry, I didn't know that. Then please look here: http://stackoverflow.com/questions/15425760/is-there-another-command-for-wm-concat-in-oracle-9i — Thorsten Kettner, Jan 07 '14 at 12:20

San · Answer 5 · 2014-01-07T03:23:53.773

Hope it helps you

  with not_in_class as 
      (select a.*
         from Names a
        where not exists ( select 'x'
                         from course b
                        where b.Course_id = a.class_id 
                          and a.Stud_name = b.Stud_name)),
    not_in_course as 
      (select b.*
         from course b
        where not exists ( select 'x'
                         from Names a
                        where b.Course_id = a.class_id 
                          and a.Stud_name = b.Stud_name))
    select x.class_id, 
           x.Stud_name NOT_IN_CLASS, 
           y.stud_name  NOT_IN_COURSE
      from not_in_class x, not_in_course y
     where x.class_id = y.course_id

Output

| CLASS_ID | NOT_IN_CLASS | NOT_IN_COURSE |
|----------|--------------|---------------|
|     S001 |        PETER |         KEITH |

Only problem is that if multiple mismatches are there in both the tables for a given id, it works for single mismatch for a particular id. You need to rework if multiple mismatches are there for the same id.

Hi , the above query showing error, its giving error, am not sure what went wrong — Rams, Jan 07 '14 at 02:10

How to fetch the non matching rows in Oracle

Table: Names

Table: Course

Output

5 Answers5