Find individual column mismatch between source and target

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  February 3, 2019 at 2:16 am

  #376937

  Hi All,

  
Source table:
CREATE TABLE EMP_CLIENT(
 ID INT     NOT NULL,
 NAME VARCHAR (20)  NOT NULL,
 AGE INT     NOT NULL,
 ADDRESS CHAR (25) ,
 SALARY DECIMAL (18, 2),  
 PRIMARY KEY (ID)
);
INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

  Target table:
CREATE TABLE EMP_PROVIDER(
 ID INT     NOT NULL,
 NAME VARCHAR (20)  NOT NULL,
 AGE INT     NOT NULL,
 ADDRESS CHAR (25) ,
 SALARY DECIMAL (18, 2),  
 PRIMARY KEY (ID)
);

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'kaushik', 23, 'Kota', 2000.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'saravanan', 25, 'Mumbai', 6500.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, '', 8.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, '', 22, 'MP', 4500.00 );


  I want to find out mismatch records so I tried below query

  SELECT * FROM EMP_CLIENT
EXCEPT 
SELECT * FROM EMP_PROVIDER


  But EXCEPT query is giving mismatch records but it is not giving on which column it is not matching .

  
To find individual mismatch record I am using below query.
SELECT
CASE WHEN CLI.ID<>PRO.ID THEN 'CLI.ID'ELSE ''END +
CASE WHEN CLI.NAME<>PRO.NAME THEN 'CLI.NAME' ELSE ''END+
CASE WHEN CLI.AGE<>PRO.AGE THEN 'CLI.AGE' ELSE ''END+
CASE WHEN CLI.[ADDRESS]<>PRO.[ADDRESS] THEN 'CLI.[ADDRESS]' ELSE ''END+
CASE WHEN CLI.SALARY<>PRO.SALARY THEN 'CLI.SALARY'ELSE ''END AS MATCHING_RECORDS,
CLI.ID,CLI.NAME,CLI.AGE,CLI.[ADDRESS],CLI.SALARY
FROM EMP_CLIENT CLI
LEFT OUTER JOIN
EMP_PROVIDER PRO
ON CLI.ID=PRO.ID
WHERE
(CLI.ID<>PRO.ID OR CLI.NAME<>PRO.NAME OR CLI.AGE<>PRO.AGE
OR CLI.[ADDRESS]<>PRO.[ADDRESS] OR CLI.SALARY<>PRO.SALARY)


  I would like to know is there  any other better approach to find individual column mismatch between 2  tables.

  Saravanan

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  February 3, 2019 at 4:04 am

  #2020945

  Be careful here, this query will ignore any missing records in the destination table.
  😎

  Here is a quick example, the change is bold in the where clause.
  
USE TEEST;
GO
SET NOCOUNT ON;

  IF OBJECT_ID(N'dbo.EMP_CLIENT') IS NOT NULL DROP TABLE dbo.EMP_CLIENT;
CREATE TABLE dbo.EMP_CLIENT
(
  [ID] INT      NOT NULL  PRIMARY KEY CLUSTERED
 ,[NAME] VARCHAR (20)   NOT NULL
 ,[AGE] INT      NOT NULL
 ,[ADDRESS] CHAR (25)    NULL
 ,[SALARY] DECIMAL (18, 2)  NULL 
);
INSERT INTO dbo.EMP_CLIENT ([ID],[NAME],[AGE],[ADDRESS],[SALARY])
VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 )
,(2, 'Khilan', 25, 'Delhi', 1500.00 )
,(3, 'kaushik', 23, 'Kota', 2000.00 )
,(4, 'Chaitali', 25, 'Mumbai', 6500.00 )
,(5, 'Hardik', 27, 'Bhopal', 8500.00 )
,(6, 'Komal', 22, 'MP', 4500.00 );

  IF OBJECT_ID(N'dbo.EMP_PROVIDER') IS NOT NULL DROP TABLE dbo.EMP_PROVIDER;
CREATE TABLE dbo.EMP_PROVIDER
(
  [ID] INT      NOT NULL
 ,[NAME] VARCHAR (20)   NOT NULL
 ,[AGE] INT      NOT NULL
 ,[ADDRESS] CHAR (25)    NULL
 ,[SALARY] DECIMAL (18, 2)  NULL 
);

  INSERT INTO dbo.EMP_PROVIDER ([ID],[NAME],[AGE],[ADDRESS],[SALARY])
VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 )
,(2, 'Khilan', 25, 'Delhi', 1500.00 )
,(7, 'kaushik', 23, 'Kota', 2000.00 )
,(4, 'saravanan', 25, 'Mumbai', 6500.00 )
,(5, 'Hardik', 27, '', 8.00 )
,(6, '', 22, 'MP', 4500.00 );

  SELECT
  ESRC.ID
 ,ESRC.[NAME]
 ,ESRC.[AGE]
 ,ESRC.[ADDRESS]
 ,ESRC.[SALARY]
 ,CONCAT
   (
    CASE WHEN EDEST.ID IS NULL        THEN 'MISSING ID' END
    ,CASE WHEN ESRC.[NAME]  <> EDEST.[NAME]   THEN '[NAME]'   END
    ,CASE WHEN ESRC.[AGE]  <> EDEST.[AGE]   THEN '[AGE]'   END
    ,CASE WHEN ESRC.[ADDRESS] <> EDEST.[ADDRESS]  THEN '[ADDRESS]'  END
    ,CASE WHEN ESRC.[SALARY] <> EDEST.[SALARY]  THEN '[SALARY]'  END
   ) AS DIFF_STR

  FROM    dbo.EMP_CLIENT    ESRC
LEFT OUTER JOIN dbo.EMP_PROVIDER   EDEST
ON     ESRC.ID     = EDEST.ID
WHERE EDEST.ID   IS NULL
OR  ESRC.[NAME]  <> EDEST.[NAME] 
OR  ESRC.[AGE]  <> EDEST.[AGE] 
OR  ESRC.[ADDRESS] <> EDEST.[ADDRESS]
OR  ESRC.[SALARY] <> EDEST.[SALARY]
;


• okfine08

  SSC Rookie

  Points: 49

  More actions

  February 3, 2019 at 6:25 am

  #2020956

  HI 

  THIS IS A DIFFERENT WAY OF DOING THIS
  EXPERTS CORRECT ME IF I AM MISSING ANYTHING

  🙂
  🙂

  DROP TABLE EMP_CLIENT
GO
CREATE TABLE EMP_CLIENT(
ID INT  NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT  NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2), 
PRIMARY KEY (ID)
);
INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

  DROP TABLE EMP_PROVIDER
GO

  CREATE TABLE EMP_PROVIDER(
ID INT  NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT  NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2), 
PRIMARY KEY (ID)
);

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'kaushik', 23, 'Kota', 2000.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'saravanan', 25, 'Mumbai', 6500.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, '', 8.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, '', 22, 'MP', 4500.00 );

  SELECT * FROM EMP_PROVIDER
GO

  SELECT * FROM EMP_CLIENT
GO

  SELECT A.*,'ID DIFFERENT' FROM EMP_PROVIDER A JOIN EMP_CLIENT B
ON A.ID <> B.ID AND A.NAME = B.NAME
UNION ALL
SELECT A.*,'AGE DIFFERENT' FROM EMP_PROVIDER A JOIN EMP_CLIENT B
ON A.AGE <> B.AGE AND A.NAME = B.NAME
UNION ALL
SELECT A.*,'ADDRESS DIFFERENT' FROM EMP_PROVIDER A JOIN EMP_CLIENT B
ON A.ADDRESS <> B.ADDRESS AND A.NAME = B.NAME
UNION ALL
SELECT A.*,'SALARY DIFFERENT' FROM EMP_PROVIDER A JOIN EMP_CLIENT B
ON A.SALARY <> B.SALARY AND A.NAME = B.NAME
UNION ALL
SELECT A.*,'NAME DOES NOT EXIST' FROM EMP_PROVIDER A LEFT JOIN EMP_CLIENT B
ON A.NAME = B.NAME WHERE B.NAME IS NULL

  

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  February 3, 2019 at 6:57 am

  #2020959

  okfine08 - Sunday, February 3, 2019 6:25 AM

  HI 

  THIS IS A DIFFERENT WAY OF DOING THIS
  EXPERTS CORRECT ME IF I AM MISSING ANYTHING

  🙂
  🙂

  DROP TABLE EMP_CLIENT
GO
CREATE TABLE EMP_CLIENT(
ID INT  NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT  NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2), 
PRIMARY KEY (ID)
);
INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

  DROP TABLE EMP_PROVIDER
GO

  CREATE TABLE EMP_PROVIDER(
ID INT  NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT  NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2), 
PRIMARY KEY (ID)
);

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'kaushik', 23, 'Kota', 2000.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'saravanan', 25, 'Mumbai', 6500.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, '', 8.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, '', 22, 'MP', 4500.00 );

  SELECT * FROM EMP_PROVIDER
GO

  SELECT * FROM EMP_CLIENT
GO

  SELECT A.*,'ID DIFFERENT' FROM EMP_PROVIDER A JOIN EMP_CLIENT B
ON A.ID <> B.ID AND A.NAME = B.NAME
UNION ALL
SELECT A.*,'AGE DIFFERENT' FROM EMP_PROVIDER A JOIN EMP_CLIENT B
ON A.AGE <> B.AGE AND A.NAME = B.NAME
UNION ALL
SELECT A.*,'ADDRESS DIFFERENT' FROM EMP_PROVIDER A JOIN EMP_CLIENT B
ON A.ADDRESS <> B.ADDRESS AND A.NAME = B.NAME
UNION ALL
SELECT A.*,'SALARY DIFFERENT' FROM EMP_PROVIDER A JOIN EMP_CLIENT B
ON A.SALARY <> B.SALARY AND A.NAME = B.NAME
UNION ALL
SELECT A.*,'NAME DOES NOT EXIST' FROM EMP_PROVIDER A LEFT JOIN EMP_CLIENT B
ON A.NAME = B.NAME WHERE B.NAME IS NULL

  

  You are missing the source row which does not exist in the destination table (ID = 3)
  😎

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  February 3, 2019 at 8:00 am

  #2020962

  Eirikur Eiriksson - Sunday, February 3, 2019 4:04 AM

  Be careful here, this query will ignore any missing records in the destination table.
  😎

  Here is a quick example, the change is bold in the where clause.
  
USE TEEST;
GO
SET NOCOUNT ON;

  IF OBJECT_ID(N'dbo.EMP_CLIENT') IS NOT NULL DROP TABLE dbo.EMP_CLIENT;
CREATE TABLE dbo.EMP_CLIENT
(
  [ID] INT      NOT NULL  PRIMARY KEY CLUSTERED
 ,[NAME] VARCHAR (20)   NOT NULL
 ,[AGE] INT      NOT NULL
 ,[ADDRESS] CHAR (25)    NULL
 ,[SALARY] DECIMAL (18, 2)  NULL 
);
INSERT INTO dbo.EMP_CLIENT ([ID],[NAME],[AGE],[ADDRESS],[SALARY])
VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 )
,(2, 'Khilan', 25, 'Delhi', 1500.00 )
,(3, 'kaushik', 23, 'Kota', 2000.00 )
,(4, 'Chaitali', 25, 'Mumbai', 6500.00 )
,(5, 'Hardik', 27, 'Bhopal', 8500.00 )
,(6, 'Komal', 22, 'MP', 4500.00 );

  IF OBJECT_ID(N'dbo.EMP_PROVIDER') IS NOT NULL DROP TABLE dbo.EMP_PROVIDER;
CREATE TABLE dbo.EMP_PROVIDER
(
  [ID] INT      NOT NULL
 ,[NAME] VARCHAR (20)   NOT NULL
 ,[AGE] INT      NOT NULL
 ,[ADDRESS] CHAR (25)    NULL
 ,[SALARY] DECIMAL (18, 2)  NULL 
);

  INSERT INTO dbo.EMP_PROVIDER ([ID],[NAME],[AGE],[ADDRESS],[SALARY])
VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 )
,(2, 'Khilan', 25, 'Delhi', 1500.00 )
,(7, 'kaushik', 23, 'Kota', 2000.00 )
,(4, 'saravanan', 25, 'Mumbai', 6500.00 )
,(5, 'Hardik', 27, '', 8.00 )
,(6, '', 22, 'MP', 4500.00 );

  SELECT
  ESRC.ID
 ,ESRC.[NAME]
 ,ESRC.[AGE]
 ,ESRC.[ADDRESS]
 ,ESRC.[SALARY]
 ,CONCAT
   (
    CASE WHEN EDEST.ID IS NULL        THEN 'MISSING ID' END
    ,CASE WHEN ESRC.[NAME]  <> EDEST.[NAME]   THEN '[NAME]'   END
    ,CASE WHEN ESRC.[AGE]  <> EDEST.[AGE]   THEN '[AGE]'   END
    ,CASE WHEN ESRC.[ADDRESS] <> EDEST.[ADDRESS]  THEN '[ADDRESS]'  END
    ,CASE WHEN ESRC.[SALARY] <> EDEST.[SALARY]  THEN '[SALARY]'  END
   ) AS DIFF_STR

  FROM    dbo.EMP_CLIENT    ESRC
LEFT OUTER JOIN dbo.EMP_PROVIDER   EDEST
ON     ESRC.ID     = EDEST.ID
WHERE EDEST.ID   IS NULL
OR  ESRC.[NAME]  <> EDEST.[NAME] 
OR  ESRC.[AGE]  <> EDEST.[AGE] 
OR  ESRC.[ADDRESS] <> EDEST.[ADDRESS]
OR  ESRC.[SALARY] <> EDEST.[SALARY]
;


  Thanks Erikur

  Saravanan

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  February 3, 2019 at 8:03 am

  #2020963

  okfine08 - Sunday, February 3, 2019 6:25 AM

  HI 

  THIS IS A DIFFERENT WAY OF DOING THIS
  EXPERTS CORRECT ME IF I AM MISSING ANYTHING

  🙂
  🙂

  DROP TABLE EMP_CLIENT
GO
CREATE TABLE EMP_CLIENT(
ID INT  NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT  NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2), 
PRIMARY KEY (ID)
);
INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

  INSERT INTO EMP_CLIENT (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

  DROP TABLE EMP_PROVIDER
GO

  CREATE TABLE EMP_PROVIDER(
ID INT  NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT  NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2), 
PRIMARY KEY (ID)
);

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'kaushik', 23, 'Kota', 2000.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'saravanan', 25, 'Mumbai', 6500.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, '', 8.00 );

  INSERT INTO EMP_PROVIDER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, '', 22, 'MP', 4500.00 );

  SELECT * FROM EMP_PROVIDER
GO

  SELECT * FROM EMP_CLIENT
GO

  SELECT A.*,'ID DIFFERENT' FROM EMP_PROVIDER A JOIN EMP_CLIENT B
ON A.ID <> B.ID AND A.NAME = B.NAME
UNION ALL
SELECT A.*,'AGE DIFFERENT' FROM EMP_PROVIDER A JOIN EMP_CLIENT B
ON A.AGE <> B.AGE AND A.NAME = B.NAME
UNION ALL
SELECT A.*,'ADDRESS DIFFERENT' FROM EMP_PROVIDER A JOIN EMP_CLIENT B
ON A.ADDRESS <> B.ADDRESS AND A.NAME = B.NAME
UNION ALL
SELECT A.*,'SALARY DIFFERENT' FROM EMP_PROVIDER A JOIN EMP_CLIENT B
ON A.SALARY <> B.SALARY AND A.NAME = B.NAME
UNION ALL
SELECT A.*,'NAME DOES NOT EXIST' FROM EMP_PROVIDER A LEFT JOIN EMP_CLIENT B
ON A.NAME = B.NAME WHERE B.NAME IS NULL

  

  Adding to Erikur point. That you should not join with name column as it might have duplicate records and in turn results in Cartesian product. Mostly we need to join with primary key in this case with ID column

  Saravanan

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  February 3, 2019 at 5:00 pm

  #2020986

  It would appear that the ID is only unique to the table it is used in and, therefore, would not satisfy a join between the two tables.  What, other than ID, would satisfy a 1:1 join between these two table?  If you can't do that, then you can't actually identify which column caused the mismatch.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  February 3, 2019 at 9:51 pm

  #2020994

  Jeff Moden - Sunday, February 3, 2019 5:00 PM

  It would appear that the ID is only unique to the table it is used in and, therefore, would not satisfy a join between the two tables.  What, other than ID, would satisfy a 1:1 join between these two table?  If you can't do that, then you can't actually identify which column caused the mismatch.

  That is 100 % true Jeff

  Saravanan