CODEWITHSUNDEEP
sql
user4143467
user4143467

Reputation:

find difference between two tables in sql

what is the equivalent sql of this table? i want to get only the names of the persons from all_user table who are not in your_friend table

Angelina Jolie
Brad Pitt
Peter Parker
Clark Kent

table name: all_user
(users)
Angelina Jolie
Brad Pitt
Peter Parker
Mary Jane
Clark Kent
Lois Lane

table name: your_friend
(friend)
Lois Lane
Marj Jane

select distinct * from all_user where not in (select * from your_friend where all_user.users = your_friend.friend)

This is my answer and i am getting an error near IN syntax.

Upvotes: 0

Views: 77

Answers (2)

bodjo
bodjo

Reputation: 326

Try this

select distinct * from all_users where users not in (select distinct friend from your_friend)

Upvotes: 0

Mike Dinescu
Mike Dinescu

Reputation: 55720

You haven't specified a RDBMS. If your engine supports it the EXCEPT operation is what you are looking for. It evaluates the output of two query expressions and returns the difference between the results. The result set contains all rows returned from the first query expression except those rows that are also returned from the second query expression.

SELECT DISTINCT <Columns_To_Be_Included>
  FROM all_user 
EXCEPT
SELECT DISTINCT <Columns_To_Be_Included>
  FROM your_friend

But be careful that this works at the record level. So, you have to only specify the columns that you want to include in the comparison.

Upvotes: 1

Related Questions