Is there any other way I could pull data from a table where two pair columns value not the same

Question

I have a table article_pair with columns rule_id, driver_article, driver_article_uom, pair_article and pair_article_uom. The driver_article and pair_article, driver_article_uom and pair_article_uom should not be the same in the data which we select. I could do this by using the below query, but is there is any alternate approach?

SELECT * FROM article_pair
MINUS
SELECT * FROM article_pair 
    where driver_article=pair_article 
        AND driver_article_uom=pair_article_uom

In this example, artilce_pair is a table. But in my scenario, it is a sub query.

Answer 1

You can get it with Where clause using either AND or OR operator depending on do both pairs need to differ or just one of them:

WITH    --  S a m p l e    D a t a:
    article_pair (RULE_ID, DRIVER_ARTICLE, DRIVER_ARTICLE_UOM, PAIR_ARTICLE, PAIR_ARTICLE_UOM) AS
        ( Select 1, 'DA_1', 'PA_1', 'DA_1', 'PA_1' From Dual Union All
          Select 2, 'DA_2', 'PA_2', 'PAXX', 'PA_2' From Dual Union All
          Select 3, 'DA_3', 'PA_3', 'PAXX', 'PAPA' From Dual Union All
          Select 4, 'DA_4', 'UOM4', 'DA_4', 'PAXX' From Dual Union All
          Select 5, 'DA_5', 'PA_5', 'DA_5', 'PA_5' From Dual 
        )

--  S Q L :
Select  RULE_ID, DRIVER_ARTICLE, DRIVER_ARTICLE_UOM, PAIR_ARTICLE, PAIR_ARTICLE_UOM
From      article_pair
Where   DRIVER_ARTICLE != PAIR_ARTICLE
      AND     -- if just one of the pairs should be unmatched use OR instead of AND
        DRIVER_ARTICLE_UOM != PAIR_ARTICLE_UOM

/*  R e s u l t :    (with AND operator)
   RULE_ID DRIVER_ARTICLE DRIVER_ARTICLE_UOM PAIR_ARTICLE PAIR_ARTICLE_UOM
---------- -------------- ------------------ ------------ ----------------
         3 DA_3           PA_3               PAXX         PAPA             */

/*  R e s u l t :    (with OR operator)
   RULE_ID DRIVER_ARTICLE DRIVER_ARTICLE_UOM PAIR_ARTICLE PAIR_ARTICLE_UOM
---------- -------------- ------------------ ------------ ----------------
         2 DA_2           PA_2               PAXX         PA_2
         3 DA_3           PA_3               PAXX         PAPA
         4 DA_4           UOM4               DA_4         PAXX              */