How would I get records from a table, with not matching IDs from another table

Question

Firstly I would like to say that I am sorry if it is a duplicate, I really did not find anything that would help me. I am quite new to real SQL development, so if somebody could even point me to some resources I would very much appreciate it.

Problem:

I am not sure how to handle things, I suspect the need of UNION and LEFT OUTER JOIN.

Summary:

Table1: user1 history of viewed articles (1 column with article IDs)

Table2: user2 history of viewed articles (1 column with article IDs)

Table3: articles (more columns has IDs)

Now I would like an UNION between user1 and user2 history and get the IDs of articles that both have not seen yet limited to 10.

Pseudo code:

(SELECT * FROM user1 UNION SELECT * FROM user2) as UnionTable SELECT top(10) * FROM article WHERE UnionTable.id <> article.id OR SELECT * FROM articles LEFT OUTER JOIN UnionTable

The expected outcome is a list of article IDs that are not found in the union table. Are there any other better ways? Subqueries or procedures?

Yogesh Sharma · Accepted Answer · 2019-02-13 15:04:18Z

3

You need LEFT OUTER JOIN with UNION result-set, but you can also do :

WITH CTE AS ( <UNION Query here> ) SELECT TOP (10) a.* FROM article a WHERE NOT EXISTS (SELECT 1 FROM CTE c WHERE c.ID = a.ID);

You can use OUTER JOIN with filtration :

SELECT TOP (10) a.* FROM article a LEFT OUTER JOIN CTE c ON c.id = a.id WHERE c.id IS NULL;

answered Feb 13, 2019 at 15:04

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matthew Baker Over a year ago

+1 for the "where not exists" If you check the execution plans for both, you'll likely find that where not exists is more efficient and pulls less data.

krosekri Over a year ago

Thank you! I selected to be the correct answer because you also used 'with' and I did not know about it, while I really should have had...

Johann · Accepted Answer · 2019-02-13 15:10:58Z

You also can use a subquery

select * from articles a where a.id not in ( select u.id from UnionTable u )

but I prefer the left join

select * from articles a left join UnionTable u on u.id=a.id where u.id is null

Gordon Linoff · Accepted Answer · 2019-02-13 16:24:00Z

I would write this as:

SELECT TOP (10) a.* FROM article a WHERE NOT EXISTS (SELECT 1 FROM user1 v WHERE v.ID = a.ID) AND NOT EXISTS (SELECT 1 FROM user2 v WHERE v.ID = a.ID) ;

This can make use of indexes on user1(id) and user2(id).

More importantly, though, you should fix your data model. You should have one table userArticles with information like this:

create table userArticles ( userArticleId int identity(1, 1) primary key, userId int references users(userId), articleId int references articles(articleId), viewDateTime datetime default getdate() );

Spreading information such as views through multiple tables is a bad idea.

Collectives™ on Stack Overflow

How would I get records from a table, with not matching IDs from another table

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related