Oracle DB 12.1.0.2
Hello Team,
I would need some help with sql below. I would like to
remove users in 2019 who do not appear in 2021
remove users not in 2019 who appear in 2021
Sample data for 2021:
SURNAME FORENAME ADDRESS
AATCHIAMITH Jayshree Longere Sugar Planters
ABDOOL Marie Anastasia A16 Stateland,Sugar Planters
ABDOOL Mirella A16 Stateland,Sugar Planters
ABDOOLROHOMUN Bibi Shamshad 23 Morc Soobrah Phase 2
ABDULLAH Mohammad Fardeen Luqmam Ave. La Caille,Pte Aux Sables
ABEELACK Beverley Mary-Jane D 5, Police Quarters,Pointe Aux Sables
ABEELACK Kimberley Sarah Kate D 5, Police Quarters,Pointe Aux Sables
ABEL Gaetan Colbert Ave., Morc. Rey
ABEL Marie Magda Mirella Colbert Ave., Morc. Rey
ABEL Patricia Julie Colbert Ave., Morc. Rey
ACHILLE Jean Pierre Joseph Mario A54 Stateland,Sugar Planters
ACHILLE Yvonette Marie Claire A54 Stateland,Sugar Planters
ACKBURALLY Marie Cecilia Elodie Ave. Des Narcisses,Petit Verger
ACKBURALLY Mohammad Taysiir Ave. Des Narcisses,Petit Verger
ADAKALEM Kevisha Morc Sohun,Pointe Aux Sables
ADAKALEM Saradha Morc Sohun,Pointe Aux Sables
ADAKALEM Soopramanien Morc Sohun,Pointe Aux Sables
ADAKALUM Patrick Jean Marc Morc. Verger Les Bains
ADAKALUM Veronique Hemaliny Morc. Verger Les Bains
ADAM Clarel Ave Lafayette Petit Erger
ADAM Sonia Ave Lafayette Petit Erger
ADAMJEE Akhtar Hussein 19, Oliviers St.
ADAMJEE Marie Melisa Goergia 19, Oliviers St.
ADELINE Louis Janes Trochetias Ave., Morc. Rey.
ADELINE Marie Therese (B. 29.11.36) Longere Sugar Planters
ADELINE Mary Joyce Macdaleha 80, Jean Blaise St,State Land
ADELINE Ulda Trochetias Ave., Morc. Rey.
ADIMOOLAM Arusha Subashini Neptune Ave., Morc. Rey
ADIMOOLAM Ashwin Soondiren Neptune Ave., Morc. Rey
ADIMOOLAM Danalutchmee Padiachy Neptune Ave., Morc. Rey
ADRIEN Leu Judex Paquerette Ave., Morc. Ghurburrun
AGATHE Marie Fleurinette 62, Kensington Place
AH CHAM Jean Michel Laval Eddie Chan Kien Seng Badamiers Ave.,Pointe Aux Sables
AH CHAM Wong Yam Moye Badamiers Ave.,Pointe Aux Sables
AH CHAM Yung Mine Foon 12 Baissac Ave Morc Koenig
AH CHOON Ken Vince Lee Colbert Ave., Morc. Rey
AH CHOON Michel Eude Colbert Ave., Morc. Rey
AH CHOON Michelle Colette Colbert Ave., Morc. Rey
AH CHOUNG Chan Phong Choy 25, Verger Sur Mer, Petit Verger
AH CHOUNG Giovanni Alexandre 25, Verger Sur Mer, Petit Verger
AH CHOUNG Johnny 25, Verger Sur Mer, Petit Verger
AH FOCK Marie Anne Mirella 5, L. Masson Sugar Planters
AH FOCK Marie Cyndy Condith 5, L. Masson Sugar Planters
AH FOCK Marie Quincyla Jennyfer Royal Rd.,Pointe Aux Sables
AH FOCK Peter Kinsley Terry 5, L. Masson Sugar Planters
AH FOCK Peter Louis 5, L. Masson Sugar Planters
AH FOOK Annabelle 8, Rue Coqueluches,Pointe Aux Sables
AH FOOK Louis Steeve 8, Rue Coqueluches,Pointe Aux Sables
AH HANG Lisette Therese Ave. Des Novembrier, Morc. Rey
AH HING Jean Pierre Noel Cactus Ave., Morc. Rey
AH HING Paul Patrick Cactus Ave., Morc. Rey
AHKEE Lourdes Catriona Valerie A45, Sugar Planters Stateland
AH KYE Danielle Chee Fan Novembriers Ave., Morc. Rey
AH KYE Patrick Richard Gino Novembriers Ave., Morc. Rey
AH KYE Urbain Novembriers Ave., Morc. Rey
AHMEE Johnny Wong Kwet Yin Ave. Pailles En Queue,Morc. Nazroo
AH MEE Marie Sylvana Ave. Pailles En Queue,Morc. Nazroo
AH-MIN Brian Jerry Sanassee Garden
AH NIEN Bryan Jordy Rue Aiglons, Morc. Le Breton
AH NIEN Desire Steve Rue Aiglons, Morc. Le Breton
AH NIEN Foo Souye Chan Rue Aiglons, Morc. Le Breton
AH NIEN Marie Prisca Anoushka Rue Aiglons, Morc. Le Breton
AH PEW Marie Claire Margaret Flamingo Ave., Morc. Rey
AH PEW Michel Henry Didier Flamingo Ave., Morc. Rey
AHROODKHAM Faranah Bibi 27, Colbert Ave, Morc Rey
AHROODKHAN Mohammad Sakirkhan 27, Colbert Ave, Morc Rey
AH-SEE Michel Ange Laval 15 Flat Cote Ouest,Petit Verger
AH SHEE TEE Cyril Paul Yee Seen Fat Coqueluche Ave, Morc. Rey
AH SHEE TEE Niat Ngo Coqueluche Ave, Morc. Rey
AH SONG Sew Chin 17c . Les Tulipes, Petit Verger
AH SOON Jia Siw Ha Young-Kin Royal Rd., C. Lai Fat Fur
AH SUE Cecilia Sylvia Morc Verger Les Bains,Pointe Aux Sables
AH SUE David Morc Verger Les Bains,Pointe Aux Sables
AH TING Marie Kimberly Ameya Ave. Des Cerisiers
AH TION Jacque Desire Royal Rd., Imp. Printemps
AH TION Jean Christian Royal Rd., Imp. Printemps
AH TION Joseph Antoine Royal Rd., Imp. Printemps
AH TION Marie Anne Dolores Royal Rd., Imp. Printemps
AH TION Marie Margarette Royal Rd., Imp. Printemps
AH TION Marie Natacha Royal Rd., Imp. Printemps
AH VANE Edouard Patrick 3, Aloes Ave., Morc. Rey
AH YOON Pierre Antonio 20, J. B. Sugar Planter
AIME Louis Christian 67, Kensington Place, P. Verger
AIME Noela Maureen 67, Kensington Place, P. Verger
ALAIN Annaëlle Marie Charlotte Lot 70, Morc Soobrah, Phase 2
ALAIN Jonathan Yannick Federick Lot 70, Morc Soobrah, Phase 2
ALAIN Marie Stellie Pascaline 26-28, Challenger St, Verger Les Bains, Pte Aux Sables
ALBERT Adam Olivier Ave. Paquerette,Morc. Le Printemps
ALBERT Adrien Emmanuel Ave Nenuphars,Morc Rey
ALBERT Anthony Alexandre Ave. Paquerette,Morc. Le Printemps
ALBERT Deborah Anne Colombe Ave.,Petit Verger
ALBERT Gianlucca Marie Madeleine Ave., Morc. Rey
ALBERT Jean Claude 62, Kensington Place
ALBERT Jean Clint Ave Nenuphars,Morc Rey
ALBERT Jean Denis Alan Sunny Hill Ave., Morc. Rey
ALBERT Jean Didier Cedric Sunny Hill Ave., Morc. Rey
ALBERT Jean Eric Colombe Ave.,Petit Verger
ALBERT Jerome Sebastien Colombe Ave.,Petit Verger
ALBERT Marcello Sunny Hill Ave., Morc. Rey
sample data for 2019
SURNAME FORENAME ADDRESS
AATCHIAMITH Jayshree Longere Sugar Planters
ABDOOL Marie Anastasia A16 Stateland,Sugar Planters
ABDOOL Mirella A16 Stateland,Sugar Planters
ABDOOLROHOMUN Bibi Shamshad 23 Morc Soobrah Phase 2
ABEL Gaetan Colbert Ave., Morc. Rey
ABEL Marie Magda Mirella Colbert Ave., Morc. Rey
ABEL Patricia Julie Colbert Ave., Morc. Rey
ACHILLE Jean Pierre Joseph Mario A54 Stateland,Sugar Planters
ACHILLE Yvonette Marie Claire A54 Stateland,Sugar Planters
ADAKALEM Kevisha Morc Sohun,Pointe Aux Sables
ADAKALEM Saradha Morc Sohun,Pointe Aux Sables
ADAKALEM Soopramanien Morc Sohun,Pointe Aux Sables
ADAKALUM Patrick Jean Marc Morc. Verger Les Bains
ADAKALUM Veronique Hemaliny Morc. Verger Les Bains
ADAM Clarel Ave Lafayette Petit Erger
ADAM Sonia Ave Lafayette Petit Erger
ADELINE Louis Janes Trochetias Ave., Morc. Rey.
ADELINE Marie Therese (B. 29.11.36) Longere Sugar Planters
ADELINE Mary Joyce Macdaleha 80, Jean Blaise St,State Land
ADELINE Ulda Trochetias Ave., Morc. Rey.
ADIMOOLAM Arusha Subashini Neptune Ave., Morc. Rey
ADIMOOLAM Ashwin Soondiren Neptune Ave., Morc. Rey
ADIMOOLAM Danalutchmee Padiachy Neptune Ave., Morc. Rey
ADRIEN Leu Judex Paquerette Ave., Morc. Ghurburrun
AGATHE Marie Nadine Rosemay Golden Lane,Morc. Rey
AH CHAM Yung Mine Foon 12 Baissac Ave Morc Koenig
AH CHOON Ken Vince Lee Colbert Ave., Morc. Rey
AH CHOON Michel Eude Colbert Ave., Morc. Rey
AH CHOON Michelle Colette Colbert Ave., Morc. Rey
AH CHOUNG Chan Phong Choy 25, Verger Sur Mer, Petit Verger
AH CHOUNG Giovanni Alexandre 25, Verger Sur Mer, Petit Verger
AH CHOUNG Johnny 25, Verger Sur Mer, Petit Verger
AH FOCK Marie Anne Mirella 5, L. Masson Sugar Planters
AH FOCK Marie Quincyla Jennyfer Royal Rd.,Pointe Aux Sables
AH FOCK Peter Kinsley Terry 5, L. Masson Sugar Planters
AH FOCK Peter Louis 5, L. Masson Sugar Planters
AH FOOK Annabelle 8, Rue Coqueluches,Pointe Aux Sables
AH HANG Lisette Therese Ave. Des Novembrier, Morc. Rey
AH HING Jean Pierre Noel Cactus Ave., Morc. Rey
AH HING Paul Patrick Cactus Ave., Morc. Rey
AH KYE Danielle Chee Fan Novembriers Ave., Morc. Rey
AH KYE Patrick Richard Gino Novembriers Ave., Morc. Rey
AH KYE Urbain Novembriers Ave., Morc. Rey
AH MEE Marie Sylvana Ave. Pailles En Queue,Morc. Nazroo
AH NIEN Bryan Jordy Rue Aiglons, Morc. Le Breton
AH NIEN Desire Steve Rue Aiglons, Morc. Le Breton
AH NIEN Foo Souye Chan Rue Aiglons, Morc. Le Breton
AH NIEN Marie Prisca Anoushka Rue Aiglons, Morc. Le Breton
AH PEW Marie Claire Margaret Flamingo Ave., Morc. Rey
AH PEW Michel Henry Didier Flamingo Ave., Morc. Rey
AH SHEE TEE Cyril Paul Yee Seen Fat Coqueluche Ave, Morc. Rey
AH SHEE TEE Niat Ngo Coqueluche Ave, Morc. Rey
AH SONG Sew Chin 17c . Les Tulipes, Petit Verger
AH SOON Jia Siw Ha Young-Kin Royal Rd., C. Lai Fat Fur
AH SUE Cecilia Sylvia Acacias Ave., Morc. Rey
AH SUE David Morc Verger Les Bains,Pointe Aux Sables
AH TING Marie Kimberly Ameya Ave. Des Cerisiers
AH TION Jacque Desire Royal Rd., Imp. Printemps
AH TION Jean Christian Royal Rd., Imp. Printemps
AH TION Joseph Antoine Royal Rd., Imp. Printemps
AH TION Marie Anne Dolores Royal Rd., Imp. Printemps
AH TION Marie Margarette Royal Rd., Imp. Printemps
AH TION Marie Natacha Royal Rd., Imp. Printemps
AH VANE Edouard Patrick 3, Aloes Ave., Morc. Rey
AH YOON Pierre Antonio 20, J. B. Sugar Planter
AH-MIN Brian Jerry Sanassee Garden
AH-SEE Michel Ange Laval 15 Flat Cote Ouest,Petit Verger
AHFOOK Louis Steeve 8, Rue Coqueluches,Pointe Aux Sables
AHMEE Johnny Wong Kwet Yin Ave. Pailles En Queue,Morc. Nazroo
AHROODKHAM Faranah Bibi 27, Colbert Ave, Morc Rey
AHROODKHAN Mohammad Sakirkhan 27, Colbert Ave, Morc Rey
AIME Louis Christian 67, Kensington Place, P. Verger
AIME Noela Maureen 67, Kensington Place, P. Verger
AL FARWAN-GOKOOL Rimi C01, Residence Rubis.,Pointe Aux Sables
ALAIN Jonathan Yannick Federick Lot 70, Morc Soobrah, Phase 2
ALAIN Marie Stellie Pascaline 26-28, Challenger St, Verger Les Bains, Pte Aux Sables
ALBERT Adam Olivier Ave. Paquerette,Morc. Le Printemps
ALBERT Adrien Emmanuel Ave Nenuphars,Morc Rey
ALBERT Anthony Alexandre Ave. Paquerette,Morc. Le Printemps
ALBERT Deborah Anne Colombe Ave.,Petit Verger
ALBERT Jean Claude 62, Kensington Place
ALBERT Jean Clint Ave Nenuphars,Morc Rey
ALBERT Jean Denis Alan Sunny Hill Ave., Morc. Rey
ALBERT Jean Didier Cedric Sunny Hill Ave., Morc. Rey
ALBERT Jean Eric Colombe Ave.,Petit Verger
ALBERT Jerome Sebastien Colombe Ave.,Petit Verger
ALBERT Marcello Sunny Hill Ave., Morc. Rey
ALBERT Marie Ange Pamela Sunny Hill Ave., Morc. Rey
ALBERT Marie Chantale 47, Acacias Ave.,Morc. Rey
ALBERT Marie Danie Ave Nenuphars,Morc Rey
ALBERT Marie Edwige Gina Ave. Paquerette,Morc. Le Printemps
ALBERT Marie Emilie Melissa 16 Ave Mouettes,Morc Koenig
ALBERT Marie Oceane Laurie Anais Ave. Paquerette,Morc. Le Printemps
ALBERT Marie Sandra Colombe Ave.,Petit Verger
ALBERT Marie Wendy Pascalina Flat Petit Rose B,Pte Aux Sables
ALBERT Michel Eric Flat Petit Rose B,Pte Aux Sables
ALBERT Richard Thomas Ave. Paquerette,Morc. Le Printemps
ALBERT Saurenza Mohini 11, Marie Madeleine Ave., Morc. Rey
ALBERT Zachee 47, Acacias Ave.,Morc. Rey
create table:
create table BIREPORT.TAB_CANDIDATES_2019(
SURNAME VARCHAR2(40),
FORENAME VARCHAR2(40),
ADDRESS VARCHAR2(100));
create table BIREPORT.TAB_CANDIDATES_2021(
SURNAME VARCHAR2(40),
FORENAME VARCHAR2(40),
ADDRESS VARCHAR2(100));
Kindly help.
I tried below sql but it returns records in both table
select distinct(tab1.surname) from BIREPORT.TAB_CANDIDATES_2019 tab1,BIREPORT.TAB_CANDIDATES_2021 tab2 where tab1.surname!=tab2.surname;
Kindly help.
Roshan