0

Is there a way to compare two identical tables and retrieve differential rows in Informix DB?

Consider the below tables, where ID is the primary key.

TABLE A     

ID    NAME    EMAIL               PROFILE
a1    ABC     [email protected]       student  
a2    XYZ     [email protected]       student
a3    LMN     [email protected]       Staff

TABLE B

ID    NAME    EMAIL               PROFILE
a1    ABC     [email protected]       student
a2    XYZ     [email protected]       student
a3    LMN     [email protected]       student

Kindly help me in framing an SQL to retrieve rows from TABLE A which is not matching with TABLE B.

OUTPUT should be:

ID    NAME    EMAIL               PROFILE
a3    LMN     [email protected]       Staff
Peter.Fox
  • 55
  • 1
  • 1
  • 6

1 Answers1

0
SELECT  *
FROM    TABLE_A 
            LEFT JOIN TABLE_B   
                ON TABLE_A.ID = TABLE_B.ID
                    AND TABLE_A.[NAME] = TABLE_B.[NAME]
                    AND TABLE_A.EMAIL = TABLE_B.ID
                    AND TABLE_A.[PROFILE] = TABLE_B.[PROFILE]
WHERE   TABLE_B.[NAME] IS NULL
Simone Bosio
  • 35
  • 1
  • 6
  • Be aware that the square bracket quoting notation is peculiar to MS SQL Server and Sybase — other DBMS do not support it. In Informix, the use of 'name' and 'profile' as column names would not cause any issues unquoted. – Jonathan Leffler Jun 22 '17 at 20:48
  • Thanks Simone & Jonathan – Peter.Fox Jun 23 '17 at 11:44