Tag Archives: Intersect

Comparing Two Tables to Finding Missing Rows & Matching Rows

Comparing Two Tables to Finding Missing Rows & Matching Rows

Find rows in one table that are missing from the other.

You can used MINUS operator to compare the two sets of data or Select queries and show data missing from one table.

Note: select queries must be by identical in terms of columns number and datatypes

Example : you are working on school and fetch the data that this students pay all the dues or not both having seperate tables.
Master table Students_master having information of all students.
Pay table Students_dues having information which students pay dues.

Then you can compare this for find the pending dues list for the students.

select id from students_master;
id
--
1
2
3
4
5

Select id from students_dues;

Id
--
2
4
5

select Id from students_master;
minus
select id from students_dues;

ID
--
1
3

Finding the matching rows between the tables

INTERSECT operator is used to show the matching data between the two tables.

Note: select queries must be by identical in terms of columns number and datatypes

Examples: You want to see the student which pay the dues;
Master student table: All students information like name and address present in master table (students_master)
Dues student table: All paid students list is present in this table ( Students_dues)

select id from students_master
intersect
select id from students_dues

If you want to find additional information such as name

select first_name,address from students_master where id in
(select id from students_master
intersect
select id from students_dues
);

Advertisements