Left outer join SQL Server

Question

I'm confused as to why this is not working, I've used this type of syntax several times but this one as got me pulling my hair out.

I have two tables;

tableA

regId name    regStatus
1     George  1
2     Jenny   1
3     Penny   1
4     James   1
5     Preston 1
6     Jamie   0

TableB

activeRegId passiveRegID Status
1            2           1
1            3           1
1            4           0
6            1           1

What I'm trying to do is return all rows from tableA excluding those where (tableA.regstatus = 0) and (tableB.status = 1 for a user regid = 1).

I want to avoid having to use NOT IN (select ...).

My query so far:

    select top 10 
        tA.regId, tA.name
    from 
        tableA tA 
    left OUTER JOIN 
        tableB tB ON tB.activeRegId = tA.regid AND tB.passiveRegID <> 1 
                     AND tB.status <> 1 AND tB.passiveRegID IS NULL
    where 
        tA.regStatus = 1
        and tA.regid <> 1

What I'm expecting back should be as follows however, I'm getting all the users in tableA except for Jamie.

regId name
4     James
5     Preston

Chamila Chulatunga · Accepted Answer · 2012-12-09T03:23:21.390

1

I think you need to move some of the predicates out of the ON clause of the LEFT OUTER JOIN, and instead have them as predicates in the WHERE clause.

Based on the data you provided, I can't be sure exactly what the predicates should be, however any predicates you include in the ON clause of a LEFT OUTER JOIN only serve to exclude rows from Table B, not Table A.

With a LEFT OUTER JOIN, you will still get all records of Table A unless they are excluded by predicates in the WHERE clause.

EDIT Based on comments, I think this would work, where 'loggedInUserId' is the regId from Table A of the logged in user:

SELECT top 10
   tA.regId,
   tA.name
FROM tableA tA1
JOIN tableA tA2
ON (tA1.regId <> tA2.regId
    AND tA2.regStatus <> 0)
LEFT OUTER JOIN tableB tB
ON (tA1.regId = tB.activeRegId
    AND tA2.regId = tB.passiveRegID
    AND tB.Status <> 0)
WHERE tA1.regId = 'loggedInUserId'
AND tB.activeRegId IS NULL

To also exclude those who have blocked the logged in user:

SELECT top 10
   tA.regId,
   tA.name
FROM tableA tA1
JOIN tableA tA2
ON (tA1.regId <> tA2.regId
    AND tA2.regStatus <> 0)
LEFT OUTER JOIN tableB tB
ON (
    --finding blocked users
    (tA1.regId = tB.activeRegId
     AND tA2.regId = tB.passiveRegID
     AND tB.Status <> 0)
    OR
    --finding blocking users
    (tA2.regId = tB.activeRegId
     AND tA1.regId = tB.passiveRegId
     AND tB.Status <> 0)
    )
WHERE tA1.regId = 'loggedInUserId'
AND tB.activeRegId IS NULL

edited Dec 09 '12 at 03:23

answered Dec 09 '12 at 02:25

Chamila Chulatunga

4,856
15
17

Your suggested answer returns the same data as i have from my original query. You mentioned with with a bit more detail/data, the tables i have are just like that (with a few more columns for tableA dob,lastname etc..) tableA=userDetails tableB=blocks. So a user from tableA will be able to see everyone from tableA except for themselves, however, they can block anyone they wish. So tableB holds ids of where regid=1 (aka activeRegID) has decided not to see (regid=2,3,4 aka passiveRegId), regStatus=0 (deleted user) tableB.status=0 (user was blocked but has been unblocked, I need to keep history). – david-l Dec 09 '12 at 02:59
Ah, and so you want a query that lists who can see whom? Or just the list of people George can see (since George is the only one who has set up 'blocks')? – Chamila Chulatunga Dec 09 '12 at 03:03
Correct, assuming George is logged in, he will only see James and Preston. If Preston is logged in, he will see everyone except Jamie, as Jamie has been deleted. – david-l Dec 09 '12 at 03:08
The query below does it, but i dont want to use 'IN' because i have a large database. Also, off the back of this answer i will extend the query by adding anyone who has blocked George should also not be in the list. SELECT top 10 tA.regId FROM tableA tA where tA.regId NOT IN (SELECT passiveRegID from tableB WHERE activeRegId = 1 AND status=1) AND tA.regStatus = 1 AND tA.regId <> 1 – david-l Dec 09 '12 at 03:11
That did the job, thank you for your time. I've added some comments to Glenn's post if you are interested in reading about the execution plans between yours, Glenn's and my 'NOT IN' solutions. Thanks again. – david-l Dec 09 '12 at 03:52
Just notice you added the blocked and blocking. Thank you so much, couldn't have asked for more. – david-l Dec 09 '12 at 03:57
Pleasure - and thanks for the execution plan info, it was very interesting to see how the different methods compared. – Chamila Chulatunga Dec 09 '12 at 04:00

score 1 · Answer 2 · answered Dec 09 '12 at 03:06

1

If you don't want to use NOT IN, what about using EXCEPT to exclude those you don't want?

SELECT regId
  FROM tableA
EXCEPT
SELECT tA.regId
  FROM tableA tA
  JOIN tableB tB ON (tB.activeRegId = tA.regid AND tB.passiveRegID = 1 AND tB.status = 1)
  WHERE tA.regStatus = 0

answered Dec 09 '12 at 03:06

Glenn

8,932
2
41
54

I've never used EXCEPT and didn't even know you there was such a thing. How embarrassing! Do you know if using EXCEPT has better performance than 'NOT IN' or using left outer join? – david-l Dec 09 '12 at 03:23
Will depend on the situation, but `EXCEPT` and `UNION` usually end up being some of the simplest set operations. Other flavours of sql (like Oracle) use the term `MINUS` rather than `EXCEPT'. – Glenn Dec 09 '12 at 03:27
I've been working with Oracle over the past 6 months and noticed little gems such as MINUS, but i've never seen anyone use EXCEPT in MSSQL. Just in case you were wondering, the execution plan is almost identical to chamila_c solution 2 nested loops with 0% cost and with 2 index seek and 1 index scan. Funnily enough, 'NOT IN' showed the least with 1 nested loop and 2 index scans. Anyway, i think i'll go with chamila_c solution as it will be easier to extend it with the other stuff i need to add to it. Thank you for your help and because of you i've learnt something new today. – david-l Dec 09 '12 at 03:49

Left outer join SQL Server

2 Answers2