How to perform a LEFT JOIN in SQL Server between two SELECT statements?

Question

I have two SELECT statements in SQL Server like these:

(SELECT [UserID] FROM [User])
(SELECT [TailUser], [Weight] FROM [Edge] WHERE [HeadUser] = 5043)

I want to perform a LEFT JOIN between these two SELECT statements on [UserID] attribute and [TailUser] attribute. I want to join existent records in second query with the corresponding records in first query and NULL value for absent records. How can I do this?

This question should be reopened, as left joining two select statements is as general and specific a task as any; the comment "this question is unlikely to help any future visitors" is false, as it helped me. — Aleister Tanek Javas Mraz, Aug 19 '20 at 15:08

score 104 · Accepted Answer · answered Oct 25 '11 at 14:05

104

SELECT * FROM 
(SELECT [UserID] FROM [User]) a
LEFT JOIN (SELECT [TailUser], [Weight] FROM [Edge] WHERE [HeadUser] = 5043) b
ON a.UserId = b.TailUser

answered Oct 25 '11 at 14:05

Derek

21,828
7
53
61

score 8 · Answer 2 · answered Oct 25 '11 at 14:05

8

SELECT [UserID] FROM [User] u LEFT JOIN (
SELECT [TailUser], [Weight] FROM [Edge] WHERE [HeadUser] = 5043) t on t.TailUser=u.USerID

answered Oct 25 '11 at 14:05

Icarus

63,293
14
100
115

score 3 · Answer 3 · 2011-10-25T14:10:50.540

3

select *
from user
left join edge
on user.userid = edge.tailuser
and edge.headuser = 5043

edited Oct 25 '11 at 14:10

answered Oct 25 '11 at 14:05

score 0 · Answer 4 · edited Apr 29 '21 at 13:14

0

Try this:

SELECT user.userID, edge.TailUser, edge.Weight 
FROM user
LEFT JOIN edge ON edge.HeadUser = User.UserID
WHERE edge.HeadUser=5043

OR

AND edge.HeadUser=5043

instead of a WHERE clause.

edited Apr 29 '21 at 13:14

James Skemp

8,018
9
64
107

answered Oct 25 '11 at 14:07

devasia2112

5,844
6
36
56

How to perform a LEFT JOIN in SQL Server between two SELECT statements?

4 Answers4

Linked