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?

  • 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 May 21 '18 at 8:49
  • Yes they are a primary key, but the question is valid even if they weren't. – Ezeq May 21 '18 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 May 21 '18 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. – Colin 't Hart May 22 '18 at 9:58
  • I was using MS SQL Manager – Ezeq Jun 7 '18 at 13:19

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.

| improve this answer | |
  • Thanks, that was what I needed. As for the 2) You have teach me the "select 1", was really lost there. – Ezeq May 21 '18 at 23:22

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):

FROM b.table
WHERE (c1, c2, c3) NOT IN (SELECT c1, c2, c3 FROM a.table);
| improve this answer | |

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

  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

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 =.

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

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
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.

| improve this answer | |
  • 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). – ypercubeᵀᴹ May 24 '18 at 10:58

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)

| improve this answer | |
  • 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 May 21 '18 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) – ypercubeᵀᴹ May 21 '18 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 May 22 '18 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') – ypercubeᵀᴹ May 22 '18 at 9:37
  • 1
    The topicstarter do not specify his RDBMS, while Your solution is RDBMS-dependent. It fails in MySQL, for example. – Akina May 22 '18 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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