CODEWITHSUNDEEP
sqlsql-server
nicholsonjf
nicholsonjf

Reputation: 1001

Full outer join misbehaving

I have two tables in the same sql-server database that store application names and usernames (among other things) from two consecutive quarters. What I want is a full join that shows users in table A that are not in table B and vis versa.

EDIT: Both table_a and table_b contain two or more records where AppName and Username have identical values, making the DISTINCT necessary. I've edited the examples below to reflect this.

table_a:

-----------------------
| AppName  | Username |
-----------------------
| app1     | jdoe     |
| app1     | jsmith   |
| app1     | jdoe     |
| app2     | jsmith   |
-----------------------

table_b:

-----------------------
| AppName  | Username |
-----------------------
| app1     | fbar     |
| app1     | jsmith   |
| app1     | jboehner |
| app1     | fbar     |
| app3     | jboehner |
-----------------------

SQL:

SELECT DISTINCT a.username as q2, b.username as q3
FROM tablea as a
FULL JOIN tableb as b
ON a.username = b.username
WHERE a.appname = 'app1'
AND b.appname = 'app1'

Expected result:

-----------------------
| q2       | q3       |
-----------------------
| jdoe     |          |
| jsmith   | jsmith   |
|          | fbar     |
-----------------------
|          | jboehner |
-----------------------

Actual result:

-----------------------
| q2       | q3       |
-----------------------
| jsmith   | jsmith   |
-----------------------

What gives??

Upvotes: 0

Views: 170

Answers (2)

dudeNumber4
dudeNumber4

Reputation: 4697

The where should simply be or not and: WHERE a.appname = 'app1' OR b.appname = 'app1'

Upvotes: -3

anon
anon

Reputation:

Using a WHERE clause on an OUTER JOINed table (in this case both of your tables) changes it to an INNER JOIN since the filter is applied to the result of the join and all conditions must be met. In your case this is not possible except on rows that match (it forces it to an INNER JOIN). I suspect this is what you meant:

SELECT DISTINCT a.username as q2, b.username as q3
FROM dbo.tablea as a
FULL OUTER JOIN dbo.tableb as b
ON a.username = b.username
AND (a.appname = b.appname)
WHERE a.appname = 'app1' OR b.appname = 'app1';

-- or

SELECT DISTINCT a.username as q2, b.username as q3
FROM dbo.tablea as a
FULL OUTER JOIN dbo.tableb as b
ON a.username = b.username
AND (a.appname = b.appname)
WHERE 'app1' IN (a.appname, b.appname);

Upvotes: 4

Related Questions