0

Imagine three data tables A, B and C. A and B share a common variable ID1. B and C share a common variable ID2. Write a pseudo query to get this result: select everything from Table B, with matching records in both Table A and B, but not in Table C.

My version of the answer is below (but I am not sure if it's correct):

Select *
From table_b
Left Join table_a
On table_b.ID1 = table_a.ID1
Where table_b.ID2 NOT IN table_c.ID2

I am very skeptical about if the Where condition would work for the given condition? Please give your opinion on my answer. Thanks!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Harriss
  • 13
  • 1
  • 8

2 Answers2

1
Select *
From table_b
Left Join table_a On table_b.ID1 = table_a.ID1
Where not exists (select * from table_c where table_c.ID2 = table_b.ID2)
IngoB
  • 2,552
  • 1
  • 20
  • 35
0

Similar to IngoB's solution but slightly more readable (IMHO)

SELECT *
FROM table_b 
LEFT OUTER JOIN table_a USING (id1)
WHERE id2 NOT IN (SELECT id2 FROM table_c)
fhossfel
  • 2,041
  • 16
  • 24
  • It depends on the data and database platform being used, but the NOT EXISTS method can perform better than NOT IN. For SQL Server, see this answer https://stackoverflow.com/questions/173041/not-in-vs-not-exists – Nathan Griffiths Jun 18 '17 at 05:04
  • Which solution is faster - if there is a difference at all - largely depends on the distribution of the data and the indexing of both tables. The last time I looked at this on Oracle the optimizer always came up with the same execution plan for both variants. – fhossfel Jun 19 '17 at 09:11
  • *Important:* My solution is only equivalent to IngoB's if there are no NULL values in TABLE_C.ID2. I assumed it was a PK column and therefore there would not be NULL values but if this is not the case Ingo's solution is the correct one. – fhossfel Jun 19 '17 at 09:12