2

I have 2 identical DBs, one of the tables has C1, C2, C3 , composite key. I'm trying to get the rows from B that are not present in DB A.

I have tried many approaches, but at this time I even can't re-use this suggestion due too the late hour.

I think it should be by something like

Select * from B
where  not exists                
     (SELECT a.C1, a.C2, a.C3
      from A.table as a, b.table as b 
      where a.C1=b.C1
          , a.C2=b.C2
          , a.C3=b.C3)

But I know that I'm totally wrong. What is the proper way to do this?

5
  • one of the tables has C1, C2, C3 , composite key You mean it's primary key of the table? or simply the table' structure contain index composed from those 3 fields?
    – Akina
    Commented May 21, 2018 at 8:49
  • Yes they are a primary key, but the question is valid even if they weren't.
    – Ezeq
    Commented May 21, 2018 at 21:34
  • For contextualization, after I tested the two answers I was still having unexpected results. My root error was on identifying the correct fields that composed the key while working late night. None the less, my bad foundations only bring doubts. The good answers, like @4per's, bring knowledge
    – Ezeq
    Commented May 21, 2018 at 23:41
  • 1
    Which DBMS are you using? Even if this seems very basic SQL, not in with multiple columns isn't supported by all DBMSes, so it's helpful to know which you're using. Commented May 22, 2018 at 9:58
  • I was using MS SQL Manager
    – Ezeq
    Commented Jun 7, 2018 at 13:19

5 Answers 5

6

I think you need this:

Select * from b.table as b 
where  not exists                
     (SELECT 1
      from A.table as a 
      where a.C1=b.C1 and a.C2=b.C2 and a.C3=b.C3 ) ;
  1. You shouldn't add b.table in the subquery's from clause, because you already have it under the b alias in the main query.

  2. You don't need to specify columns in the subquery's select clause, because that doesn't have any effect. "Is there a row satisfying the where clause?" – that's the question that the exists predicate is meant to answer.

  3. You need AND between the conditions in WHERE, not commas.

1
  • Thanks, that was what I needed. As for the 2) You have teach me the "select 1", was really lost there.
    – Ezeq
    Commented May 21, 2018 at 23:22
4

Historically, DBMSes had trouble with NOT IN. But most DBMSes today can handle it without problem. Not all DBMSes support this syntax with multiple columns (SQL Server doesn't for example), but many do (PostgreSQL, Oracle, MySQL). This should work for those (but untested):

SELECT *
FROM b.table
WHERE (c1, c2, c3) NOT IN (SELECT c1, c2, c3 FROM a.table);
3

To find rows in B that are not present in A you can LEFT JOIN A to B:

SELECT w.* 
  FROM      B.table AS w
  LEFT JOIN A.table AS z ON z.C1 <=> w.C1
                        AND z.C2 <=> w.C2
                        AND z.C3 <=> w.C3
 WHERE z.C1 IS NULL
   AND z.C2 IS NULL
   AND z.C3 IS NULL
;

Here I suppose that no record have C1, C2 and C3 all equal to NULL. LEFT JOIN substitute missed/nonexistent rows from A.table by rows filled by nulls that allow to distinguish them by WHERE.

P.S. As suggested by ypercubeᵀᴹ when rows joined on with plain = NULLs are irrelevant, but also any row containing NULL in C1 or C2 or C3 column will be rejected from the resulting set. That happens because ON require TRUE for each condition used, while A=B return NULL (not TRUE) if any operand is NULL. Therefore null-safe comparison <=> should be used instead of =.

3
  • Thanks, this also works (and I din't had any null)
    – Ezeq
    Commented May 21, 2018 at 23:26
  • Nulls are irrelevant. The query works whether there are nulls or not in C1/C2/C3 columns. Commented May 22, 2018 at 9:46
  • @ypercubeᵀᴹ Yep, null-safe <=> may cause the false results.
    – Kondybas
    Commented May 22, 2018 at 17:09
1

SQL has the EXCEPT clause. It removes all rows from the first set which are also present in the second set.

select c1, c2, c3 from B.table
except
select c1, c2, c3 from A.table

One difficult bit is that all columns are compared, so if further values are required for subsequent processing this fragment has to be in a sub-select with an additional JOIN. As you may guess this can lead to many table operations and performance difficulties.

I add this for information only. My choice would be the NOT EXISTS form.

1
  • 1
    A disadvantage of this is that we can't see other columns from B (and the implicit DISTINCT means that it may return different number of rows than the NOT EXISTS method, unless (c1,c2,c3) is the PK in B). Commented May 24, 2018 at 10:58
-6

enter image description hereThis might be the simplest code to be written

select * 
from b
where b.c1+b.c2+b.c3 not in (select a.c1+a.c2+a.c3 from a)

If column values are int try using convert for them: convert(varchar,b.c1)

10
  • if column values are int try using convert for them. You think it prevents implicit type conversion back to INT? It's in vain...
    – Akina
    Commented May 21, 2018 at 8:44
  • 2
    It may be simple but adding the 3 values is not correct way to compare triplets. This would match (1,2,3) with (3,1,2) and even (4,1,1) and (6,0,0) Commented May 21, 2018 at 8:50
  • It may be simple but adding the 3 values is not correct way to compare triplets. This would match (1,2,3) with (3,1,2) and even (4,1,1) and (6,0,0) it wont, check my screenshot for example. @ypercubeᵀᴹ please change your votes if you agreed.
    – Nayyer
    Commented May 22, 2018 at 9:34
  • 1
    Still it would fail for various reasons. Even for varchar columns, this would match ('1', '2', '34') with ('1', '23', '4') and ('12', '3', '4') Commented May 22, 2018 at 9:37
  • 1
    The topicstarter do not specify his RDBMS, while Your solution is RDBMS-dependent. It fails in MySQL, for example.
    – Akina
    Commented May 22, 2018 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.