Ask Question
Help Others
Home › Blogs › Zooz's blog

Oracle Minus Operator

Mon, 07/14/2008 - 5:05pm — Zooz
Your rating: None Average: 5 (2 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.

Bookmark/Search this post with:
  • Delicious
  • Digg
  • StumbleUpon
  • Reddit
  • Furl
  • Facebook
  • Google
  • Yahoo
  • Technorati
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.

Post new comment

The content of this field is kept private and will not be shown publicly.

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