SQL Server join where not exist on other table

Question

+-------------------+   +-------------------+   +---------------------+
|      Service      |   |       Asset       |   |     AssetService    |
+-------------------+   +-------------------+   +---------------------+
| Id   |    Name    |   | Id   |    Name    |   | AssetId | ServiceId |
|-------------------|   |-------------------|   |---------------------|
| 1    |  Service 1 |   | 1    |   Asset 1  |   |     1   |     1     |
| 2    |  Service 2 |   | 2    |   Asset 2  |   |     1   |     2     |
| 3    |  Service 3 |   | 3    |   Asset 3  |   |     2   |     2     |
+-------------------+   +-------------------+   |     2   |     3     |
                                                +---------------------+

So I have these tables. I want to get the Services that is not on AssetService where AssetId = 1 Like this:

+-------------------+
|      Service      |
| Id   |    Name    |
+-------------------+
| 3    |  Service 3 |
+-------------------+

Is this possible with just inner/left/right join? because I already tried different combinations of inner join but it's not working, like this inner join Asset a on a.Id != as.AssetId. I event tried left and right join.

Can somebody help me?

Thanks.

This isn't possible to do with just inner joins. If a Service has no rows in AssetService then the inner join will remove it from the result but it must be returned. — Martin Smith, Feb 06 '18 at 15:52

Elham Kohestani · Answer 1 · 2018-02-06T16:15:02.807

30

You can you use an intelligent left join to return non-matching rows only from left table(Service)

SELECT S.Id, S.Name FROM [Service] S
LEFT JOIN ServiceAsset SA
ON S.Id = SA.ServiceId
WHERE SA.ServiceId IS NULL

Note: INNER JOIN returns the matching rows whereas you want the non matching rows then use LEFT JOIN instead

edited Feb 06 '18 at 16:15

answered Feb 06 '18 at 15:50

Elham Kohestani

3,013
3
20
29

I wonder what's going to happen if there's a record in `AssetService` 2 and 3. I updated the table in my question – akoxi Feb 06 '18 at 16:04
then this query will return nothing – Elham Kohestani Feb 06 '18 at 16:05
I tested my answer. It will return those service records which are not in AssetService – Elham Kohestani Feb 06 '18 at 16:07
Yes, but my question includes `where AssetId = 1` – akoxi Feb 06 '18 at 16:10
Even if you include the AssetId = 1 then there will be nothing because AssetId here doesn't mean (When you want only non-matching service rows) – Elham Kohestani Feb 06 '18 at 16:12
4

I wish I could upvote this answer twice. This ought to be the accepted answer. – EJoshuaS - Stand with Ukraine May 05 '21 at 16:49
The `IS NULL` may at first be misleading to a novice... how can a non-null field be null? Well... because our join is a new table which is a combination of fields from both tables in the join, young paduwan... I seem to have to learn this over and over O.o – Larry Aug 01 '23 at 14:50

score 10 · Accepted Answer · edited Jun 20 '20 at 09:12

10

The simplest I can think of:

select * from Service
where Id not in (
    select ServiceId
    from AssetService 
    where AssetId = 1);

SQLFiddle link

I don't think it's possible using inner join, because that would only retrieve records that match some criteria and you are looking for records that do not match.

It is, however, possible to do it with left join as Ctznkane525 shows in his answer.

Edit

As jarlh pointed out in the comments, not in might lead to surprising results when there are nulls in the subquery. So, here is the not exists version:

select Id, Name
from Service s
where not exists (
    select *
    from AssetService a
    where AssetId = 1
    and ServiceId = s.Id);

SQLFiddle link

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 06 '18 at 15:35

Cristian Lupascu

39,078
16
100
137

The NOT IN behavior surprise many people when null values are returned from the subquery. That's why I usually do NOT EXISTS instead. (I didn't downvote.) – jarlh Feb 06 '18 at 15:39
@jarlh That's a new one for me. I just realized I never used `not in` with data sets that contained nulls. So, thanks for teaching me something new today! :) – Cristian Lupascu Feb 06 '18 at 15:52
1

Change the "where not in" to "where not exists" as jarlh suggested an it will produce what generally will be a superior query plan to the 'outer join where something is null.' solution. Specifically, it will produce semi-antijoin, which has been shown generally to outperform outer joins. – user3444696 Feb 06 '18 at 21:28

Michał Turczyn · Answer 3 · 2018-02-06T15:56:08.027

3

Try this:

select * from Service where Id not in (
    select ServiceId from AssetService where AssetId = 1 
    -- we have to filter out NULLs, in case of NULL values query result will be empty
    and ServiceId not null
)

It doesn't require any join.

Here is solution with join:

select Id, Name from Service
except
select S.Id, S.Name from Service S join AssetService [AS] on S.Id = [AS].ServiceId
where [AS].AssetId = 1

edited Feb 06 '18 at 15:56

answered Feb 06 '18 at 15:45

Michał Turczyn

32,028
14
47
69

1

Why does this have +1 but a seemingly identical earlier answer have -1? – Martin Smith Feb 06 '18 at 15:48

SQL Server join where not exist on other table

3 Answers3

Edit

Linked