Exclude composite key from select

Question

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?

Answer 1

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.

Answer 2

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

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

Answer 4

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.

Answer 5

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