Help us understand the problem. What is going on with this article?

【Oracle】2つのSQLの実行結果の差分を抽出する時に使っている関数

障害対応で原因調査のため、問題のSQLとこっちで予想したSQLを投げて、想定外のデータを確認する時に使う関数をご紹介します。

差分を抽出SQL

MINUS

SELECT test_col FROM test1_tbl -- (1)
MINUS
SELECT test_col FROM test2_tbl -- (2)

上記SQLを投げると、(1)の 実行結果から、(2)の実行結果を引きます。
で、残った結果だけが表示されます。

(1)の実行結果
test_col
1
2
3
(2)の実行結果
test_col
1
2

の場合に、MINUS句を使うと、「3」だけ抽出がされます。

test_col
3

(1)の結果から、(2)の結果を引く(MINUS)した結果となります。

(1)と(2)を逆に書いた場合

SELECT test_col FROM test2_tbl -- (2)
MINUS
SELECT test_col FROM test1_tbl -- (1)

(2)の結果は、(1)が全て網羅しているので、
結果は、何も表示されません。

test_col

なので、2つのSQLの実行結果が同一か?を調べたりする時にも使えます。

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away