40

I know there is a difference between INNER JOIN and FULL OUTER JOIN, I can see it, but, what is the difference between the two following: JOIN ... ON... and INNER JOIN...ON... and still yet JOIN...ON... vs FULL OUTER JOIN...ON...

Reason being is I think maybe just using JOIN is messing up a query I am working on that is posted on SO, link to question HERE.

So basically what is the syntactical difference between the actual set operations themselves?

Thank You,

Improve this question
5
  • 1
    "Cross posting" is posting the same question in different forums. Posting a link is ok and desirable.
    – choroba
    Jul 25, 2013 at 12:04
  • Ok, I'll post a link to the question, my code is return many many duplicates and it's driving me nuts...I have'nt even had my morning coffee yet lol
    – MCP_infiltrator
    Jul 25, 2013 at 12:16
  • 5
    @MCP_infiltrator take a look at probably the best explanation out there: codinghorror.com/blog/2007/10/…
    – mkk
    Jul 25, 2013 at 12:35
  • If the real problem is that your code is returning too many duplicates, you may want to ask another question where this is the main point. And you may want to post your code. When I've helped people with duplicate problems in the past, it's usually due to wrong join conditions, or occasionally due to poor mapping between the SQL data model and the relational data model.
    – Walter Mitty
    Jul 30, 2013 at 12:49
  • Hi @WalterMitty the question is posted in SO, I think my joins are the problem, but on another note I was also just curious, I'm only abour 6 months into writing SQL and self teaching, the link is in the question.
    – MCP_infiltrator
    Jul 30, 2013 at 12:55

2 Answers 2

Reset to default
53

JOIN and INNER JOIN are the same, the inner keyword is optional as all joins are considered to be inner joins unless otherwise specified. The difference between JOIN and FULL OUTER JOIN is the same as the difference between INNER JOIN and FULL OUTER JOIN.

An INNER JOIN will only return matched rows if a row in table A matches many rows in table B the table A row will be repeated with each table B row and vice versa.

A FULL OUTER JOIN will return everything an inner join does and return all unmatched rows from each table.

Improve this answer
4
  • 8
    Thank you, I didn't know that JOIN and INNER JOIN were equivalent statements.
    – MCP_infiltrator
    Jul 25, 2013 at 12:32
  • 9
    Best practice is to explicitly write INNER JOIN in order to avoid confusion.
    – Neil
    Jul 25, 2013 at 13:02
  • 1
    giving an example code in these forums beats the best professor lectures at Havard. Can you give an example please.
    – webs
    Dec 21, 2019 at 21:23
  • sqlfiddle.com/#!9/a6c585/148564, @webs
    – Janatbek Orozaly
    Jun 4, 2020 at 23:54
-1

The different between inner join and full outer join is that, Inner join compares, combines and return all output of all matched rows from both the tables (i.e if there is the common fields between both tables).

while Full Outer Join displays the matching or non-matching record of the table. When a match ID is not found, Full Outer Join returns a NULL value.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.