CODEWITHSUNDEEP
sql
user2323036
user2323036

Reputation: 1585

Why the below join query returns different result?

1st query - select * from a full outer join b on a.x = b.y where b.y = 10

2nd query - select * from a full outer join b on a.x = b.y and b.y = 10

Consider these table extensions:

 Table a Table b
 ======= =======

 x       y
 -----   -----
 1       2
 5       5
 10      10

The first query will return:

 10 10

And, the second query will return:

 1 NULL
 5 NULL
 10 10

Could you please let me know the reasons in detail ?

Upvotes: 1

Views: 69

Answers (3)

G one
G one

Reputation: 2729

The first query gives the expected result.

But the second query's result is different because you are joining the tables on both the conditions(a.x = b.y and b.y = 10). And since it is outer join, it'll print all the values which satisfy and which are NULL and so the output. I created sql fiddle so that you can understand it better

Upvotes: 0

Amjid Qureshi
Amjid Qureshi

Reputation: 602

the first has a where clause and so only shows results where it is true (b.y=10) the join will show all resuts asits a outer join

Upvotes: 0

StanislavL
StanislavL

Reputation: 57421

The second query has condition in the ON part so all the records are included even if they don't have pair in the joined table.

The first one has condition in the WHERE part so NULLs are filtered out.

Upvotes: 2

Related Questions