Full outer join not bringing all results SQL-Server

Question

I have an issue trying to retrieve all results from a join. I have set up a similar scenario in SQL fiddle and it works but in SQL Server it doesn't. I want to bring results for everything if they're either invoiced or shipped.

The result i am getting in SQL-SERVER is

|  No | Order1 | Shipdate |     No | Order1 |  InvDate |
|-----|--------|----------|--------|--------|----------|
| 111 |    222 | 17-01-18 |    111 |    222 | 24-01-18 |
| 222 |    333 | 18-01-18 |    222 |    333 | 24-01-18 |

Even if the change the join to full outer, right join i still get this result.

I would have thought if i use full outer it will bring all the results back regardless of matches but it doesnt.

What am i missing to give me the full outer result? Thanks

sql fiddle - http://sqlfiddle.com/#!18/89943/1

@daniel.shih `CROSS JOIN ` and `OUTER JOIN` are very different. One still attempts to relate the datasets based on the supplied `ON` clause (the `OUTER JOIN`) but still returns rows from both sides if no relation is made. The other, however, creates a Cartesian product using ALL the data from both datasets (`CROSS JOIN`). — Thom A, Feb 05 '18 at 12:49
"it works but in SQL Server it doesn't" & "change the join to full outer, right join" don't make sense. "bring results for everything" & "bring all the results back regardless of matches" are not clear. Your writing is not clear & you don't show desired output for your input. Please edit to be clear. That includes a [mcve]. PS Full join is left join union right join. Left join is inner join rows plus unmatched left table rows extended by nulls. What reference are you using? — philipxy, Feb 05 '18 at 14:11
Possible duplicate of [Full outer join not returning all rows?](https://stackoverflow.com/questions/16167870/full-outer-join-not-returning-all-rows) — philipxy, Jun 15 '19 at 12:11
Learn what FULL JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left & right table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right/left/both table column to be not NULL after an OUTER JOIN ON removes any rows from the table(s) extended by NULLs, ie leaves only LEFT/RIGHT/INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Jun 15 '19 at 12:11

score 9 · Accepted Answer · answered Feb 05 '18 at 12:42

This is your query:

SELECT S.No, s.Order1, s.Shipdate, i.No, i.Order1, i.InvDate 
FROM Ship S LEFT JOIN
     Invoice I
     ON s.No=i.No AND s.Order1 = i.Order1
WHERE S.Person = 1;

Changing the LEFT JOIN to FULL JOIN doesn't change anything. The WHERE clause turns the FULL JOIN into a LEFT JOIN, because non-matching rows on that table have NULL values and fail the WHERE condition.

score 0 · Answer 2 · answered Feb 05 '18 at 13:22

The OUTER JOIN query can be setup as follows:

SELECT
    S.No, s.Order1, s.Shipdate,
    i.No, i.Order1, i.InvDate 
FROM Ship S
FULL JOIN Invoice I
    ON s.No = i.No AND 
      s.Order1=i.Order1
-- WHERE S.Person = 1

and produces output

But by adding following filtering criteria to WHERE clause, the OUTER JOIN will produce exactly the same result with LEFT OUTER JOIN

WHERE S.Person = 1

Left Join

SELECT
    S.No, s.Order1, s.Shipdate,
    i.No, i.Order1, i.InvDate 
FROM Ship S
LEFT JOIN Invoice I
    ON s.No = i.No AND 
      s.Order1=i.Order1
WHERE S.Person = 1

Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what cannot be given in text. — philipxy, Feb 07 '18 at 09:49

russ · Answer 3 · 2018-02-05T14:01:53.470

In this sense, the Full Outer Join will give any rows that meet the ON clause criteria (S.[No]=I.[No] AND S.Order1=I.Order1) then any rows that don't meet the ON clause criteria but do meet the WHERE criteria.

So it is necessary to include WHERE I.Person=1 as well as WHERE S.Person=1 but qualified by the OR Clause for those rows that have not been shipped yet or have been shipped but not invoiced (if any).

SELECT S.[No],S.Order1,S.Shipdate,I.No,I.Order1,I.InvDate 
FROM Ship S FULL OUTER JOIN Invoice I 
ON S.[No]=I.[No] AND S.Order1=I.Order1 
WHERE S.Person=1 OR I.Person=1

I am assuming you are looking only for Person 1's data.

score -1 · Answer 4 · edited Feb 05 '18 at 13:50

-1

You can use cross join to get the result for this.

Code

SELECT S.No, s.Order1, s.Shipdate, i.No, i.Order1, i.InvDate 
FROM Ship S CROSS JOIN
     Invoice I
WHERE S.Person = 1;

edited Feb 05 '18 at 13:50

ChrisM

1,576
6
18
29

answered Feb 05 '18 at 13:10

sanyam jain

1
1

This isn't going g to work for a couple reasons. Firstly, the `WHERE`, secondly you've remove the OP's `ON` clause. As I discuss in the questions comments, `OUTER JOIN` and `CROSS JOIN` are quite different. Also, you might consider formatting your SQL as code. :) – Thom A Feb 05 '18 at 13:23
In Cross Join, you need not to pass the on Condition and secondly where clause is required here as it will filter out only those records which is needed in your result set. – sanyam jain Feb 07 '18 at 06:21
My point was the OP has a `ON` clause: `s.No=i.No AND s.Order1 = i.Order1`. This isn't in your query. – Thom A Feb 07 '18 at 08:50

Full outer join not bringing all results SQL-Server

4 Answers4