How can I change my INNER JOIN to an OUTER JOIN?

Question

I have 2 tables : Users and Documents.

1 User can have 0 or several documents.

I would like to display each user and their documents, but the problem : I would like to display users that have no documents, in a result like this (assume IdUsers 3 and 5 have no documents):

IdUser IdDocument DocumentName
====== ========== ============
1      1          test11.pdf
1      2          test12.pdf
1      3          test13.pdf
2      4          test21.pdf
2      5          test21.pdf
3      NULL       NULL
4      6          test41.pdf
5      NULL       NULL

Its always better to just ask a question on SO instead of actually learning even the most **VERY BASIC** things about the language or systems you are trying to use. `JOIN`s are very complicated and advanced and not something you should try to investigate in your own. — JNK, Apr 16 '12 at 14:17
Whoever upvoted this should have their head examined and voting privileges revoked. — JNK, Apr 16 '12 at 14:18
[Here's a canonical answer for you.](http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join) — JNK, Apr 16 '12 at 14:19

score 1 · Accepted Answer · answered Apr 16 '12 at 14:21

1

You will use a LEFT JOIN to perform this operation.

create table users
(
  userid int
)

create table documents
(
  documentid int,
  userid int,
  documentname varchar(10)
)

insert into users values (1)
insert into users values (2)
insert into users values (3)
insert into users values (4)

insert into documents values (1, 1, 'test')
insert into documents values (2, 1, 'test 1')
insert into documents values (3, 2, 'test 2')
insert into documents values (3, 3, 'test 3')

select *
from users u
left join documents d
on u.userid = d.userid

see a sqlfiddle for a test

You should do some research on JOINs, here is a good description of the JOINs:

A Visual Explanation of SQL Joins

answered Apr 16 '12 at 14:21

Taryn

242,637
56
362
405

1

While you're right, I'm hesitant to +1 because the signs clearly state "don't feed the fish." :-) – Aaron Bertrand Apr 16 '12 at 14:32
@AaronBertrand I only answered because we all have to start somewhere with SQL. Maybe they truly didn't know where to start. – Taryn Apr 16 '12 at 14:33
@bluefeet - This could easily have been answered by any of the most basic tutorials/intros to SQL. It's beyond a waste of time and space to have it here on SO. There's already a canonical answer about `JOIN` types, this is basically just noise. I won't -1 because of the effort you put in, but seriously, don't feed the fish. – JNK Apr 16 '12 at 14:35
I was half-kidding. But JNK's link was a good one. A bing/google search would have probably yielded it also. – Aaron Bertrand Apr 16 '12 at 14:35
@JNK point taken, I will keep it in mind for future. I guess I was feeling overly generous this morning. – Taryn Apr 16 '12 at 14:37

How can I change my INNER JOIN to an OUTER JOIN?

1 Answers1