1

I have two tables Table a and Table b.

Table b contains subset of Table a. I would like to get all id IDs of Table a which are not in Table b (id is a common column) Tried with this SQL, but its not working select name from table a where name not in 

select unique name from table a inner join table b on a.id = b.id;

Appreciate your help

Improve this question
1
  • You'll be much more productive in the future if you get good at googling before asking on SO. This is a common SQL question google.com/… Commented Jul 2, 2018 at 14:47

5 Answers 5

Reset to default
2

You need to use a LEFT OUTER JOIN here. This will retrieve all values from TableA and only those that match from TableB then in the WHERE clause you can test if TableB values are NULL, returning only TableA values that have no match:

 SELECT UNIQUE tableA.id FROM tableA LEFT OUTER JOIN tableB ON a.id = b.id WHERE b.id IS NULL;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

One way is to use JNevills way by using a left outer join.

In oracle you can also use exists:

with TableA as ( select 1 as "COL1" from dual union all select 2 from dual union all select 3 from dual union all select 4 from dual union all select 5 from dual ), TableB as ( select 1 as "COL1" from dual union all select 2 from dual union all select 3 from dual ) select * from TableA where not exists(select * from TableB where TableA.COL1 = TableB.COL1)
Improve this answer

4 Comments

This way is better for performance rather than left outer join and is null
They should be the same.
@GordonLinoff they may be the same, but dont need to. Using the exists syntax makes the optimizer use anti joins which give it the freedom to choose the order of the joins(depending on indices etc). This MAY result in faster execution. See described here. But I expected it to turn out to be the same as well before :). To verify this you can look up the execution plan where you see an additional left outer join in the left outer join syntax (OraDB12c).
@Chrᴉz . . . You are only sort-of right. I simple Google query suggests that left join is faster: asktom.oracle.com/pls/asktom/….
0

Take a look at the SQL Minus operator--sometimes implemented as Except (as in SQLite).

select id from TableA minus select id from TableB; select id from TableA except select id from TableB;
Improve this answer

1 Comment

EXCEPT is actually ANSI/ISO SQL.
0

Just "select id of a that are not present in table b"

select a.id from tab_a where a.id not in (select b.id from tab_b)
Improve this answer

3 Comments

what's the issue?
this gives a blank result
code edited, should return you the list of ID in A that do not appear in B
-1

Please try this it works for me

SELECT * FROM( SELECT a.id, a.name, COUNT(b.name) AS count FROM a LEFT OUTER JOIN b ON a.name = b.name GROUP BY a.id, a.name) C WHERE C.count=0

Improve this answer

3 Comments

This is way more complicated than it needs to be.
It works surely ! and why you think It is complicated ?? look sqlfiddle.com/#!9/972d44/1
Sorry about that, I didn't transcribe it correctly in the SQL fiddle. It does work. As for complexity, you're already left joining the tables so a simple IS NULL filter on the outer table is sufficient (Like JNevill did). The count/subquery is unnecessary.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.