What is the fastest way to compare two tables? (DBMR3010)

~ 0 min
2020-04-22 11:33

The “EXCEPT” operator is used to combine two “SELECT” statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. DBMaker does not support EXCEPT operator. Therefore, we give an alternative workaround. However, the command only compared two columns and cannot compare BLOB data.

 

       This command replaces “table t1 except table t2”.

 

select * from t1 where not exists(select 0 from t2 where t1.c1 = t2.c1 and (t1.c2 = t2.c2 or (t1.c2 is null and t2.c2 is null)));

 

       This command replaces “table t2 except table t1”

 

select * from t2 where not exists(select 0 from t1 where t1.c1 = t2.c1 and(t1.c2 = t2.c2 or (t1.c2 is null and t2.c2 is null)));

 

       For example, there are two tables, t1 and t2.

 

Table t1

 

C1       C2

======= =======

       1         1

       2         2

       3         3

 

Table t2

 

C1       C2

======= =======

       1         1

       2         2

       4         4

 

      Execute the first command to replace “t1 except t2”. You can get 3 which is in t1 but not in t2.

 

dmSQL> select * from t1 where not exists(select 0 from t2 where t1.c1 = t2.c1 and (t1.c2 = t2.c2 or (t1.c2 is null and t2.c2 is null)));

 

C1       C2

======= =======

       3        3

 

       Execute the second command to replace “t2 except t1”. You can get 4 which is in t2 but not in t1.

 

dmSQL> select * from t2 where not exists(select 0 from t1 where t1.c1 = t2.c1 and(t1.c2 = t2.c2 or (t1.c2 is null and t2.c2 is null)));

 

C1       C2     

======= =======

       4        4

 

Version: DBMaker 5.4.2

Product: Normal/Bundle

Platform: Windows/Linux

Average rating 0 (0 Votes)

You cannot comment on this entry

Tags