Oracle Minus Operator

Your rating: None Average: 3.5 (6 votes)

I find the MINUS operator very handy and use it a lot to compare tables and find out missing things.

For example suppose you want to compare Table1 with Table2.

If the two tables have the same layouts (same column names and data content), you can simply do this

SELECT * FROM <Table 1>
MINUS
SELECT * FROM <Table 2>

This query will compare each record in Table 1 to a record in table 2.
The result returned will be records in table 1 that are not in table 2.
you can reverse the MINUS order to get records in table 2 that are not in table 1

SELECT * FROM <Table 2>
MINUS
SELECT * FROM <Table 1>

You can also instead of doing select *, select specific columns, since for example the 2 tables might have been loaded and timestamped differently.

So you can do

SELECT column1,column2,column3 FROM <table1>
Minus
SELECT column1,column2,colum3 FROM <table2>

You have to make sure the column orders are the same when selecting from the tables.

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Guest's picture
Guest (not verified)
Re: Oracle Minus Operator

Record in table??????
What does that means...for now i thought that record means table???
isnt it???
Please clear it as m new to Peoplesoft

Guest's picture
Guest (not verified)
Re: Oracle Minus Operator

In normal dateBase concept for any kind of row of data we call Record and in peoplesoft record is a object when we built it table get qautomatically created.

Guest's picture
Guest (not verified)
Re: Oracle Minus Operator

In sql data is organised in a table in rows and columns
Table is a collection of records
a single record means a single row in a table.
example
suppose u have a table employee having columns empno,ename,job,hirdate,sal,comm,deptno then u will insert values for each column it would look like this and which is a single record in similar manner u can insert as many values as u want

2222,smith,manager,21-2-2011,22000,1200,20

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
3 + 2 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.