-1

I was learning SQL, but can't figure out how to write one query, my tables goes like this:

FIRST_TABLE

╔════╦══════════════╗
║ ID ║  USER        ║
╠════╬══════════════╣
║  1 ║ Jeff Atwood  ║
║  2 ║ Geoff Dalgas ║
║  3 ║ Jarrod Dixon ║
║  4 ║ Joel Spolsky ║
║  5 ║ Jeff Atwood  ║
║  6 ║ Rogger Masas ║
║  7 ║ Josh   Dixon ║
║  8 ║ Tom  Spolsky ║
╚════╩══════════════╝

SECOND_TABLE

╔════╦══════════════╦══════╗
║ ID ║  USER        ║ GROUP║
╠════╬══════════════╬══════╣
║  1 ║ Jeff Atwood  ║ 5636 ║
║  2 ║ Geoff Dalgas ║  148 ║
║  3 ║ Jarrod Dixon ║  101 ║
║  6 ║ Rogger Masas ║  148 ║
║  7 ║ Josh   Dixon ║  101 ║
║  8 ║ Tom  Spolsky ║  959 ║
╚════╩══════════════╩══════╝

And I need a query to select all from FIRST_TABLE, but remove all rows that contains same ID in SECOND_TABLE

Result should look like this

╔════╦══════════════╗
║ ID ║  USER        ║
╠════╬══════════════╣
║  4 ║ Joel Spolsky ║
║  5 ║ Jeff Atwood  ║
╚════╩══════════════╝
Moodland
  • 132
  • 5

1 Answers1

0

NOT EXISTS is usually a good method:

select t1.*
from first_table t1
where not exists (select 1 from second_table t2 where t2.id = t1.id);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786