SQL – Difference Between INNER JOIN and JOIN

Here is the follow up question to my earlier question SQL – Difference between != and Operator <> used for NOT EQUAL TO Operation. There was a pretty good discussion about this subject earlier and lots of people participated with their opinion. Though the answer was very simple but the conversation was indeed delightful and was indeed very informative. In this blog post I have another following up question to all of you. What is the difference between INNER JOIN and JOIN?

SQL - Difference Between INNER JOIN and JOIN joinvsinnerjoin

If you are working with database you will find developers use above both the kinds of the joins in their SQL Queries. Here is the quick example of the same.

Query using INNER JOIN

SELECT *
 FROM Table1
 INNER JOIN  Table2 ON Table1.Col1 = Table2.Col1

Query using JOIN

SELECT *
 FROM Table1
 JOIN  Table2 ON Table1.Col1 = Table2.Col1

The question is what is the difference between above two syntax.

Here is the answer – They are equal to each other. There is absolutely no difference between them. They are equal in performance as well as implementation. JOIN is actually shorter version of INNER JOIN.

Personally I prefer to write INNER JOIN because it is much cleaner to read and it avoids any confusion if there is related to JOIN. For example if users had written INNER JOIN instead of JOIN there would have been no confusion in mind and hence there was no need to have original question.

Here is the question back to you –

Which one of the following syntax do you use when you are inner joining two tables – INNER JOIN or JOIN? and Why?

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL – What ACID stands in the Database? – Contest to Win 24 Amazon Gift Cards and Joes 2 Pros 2012 Kit
Next Post
SQL SERVER – Delay Command in SQL Server – SQL in Sixty Seconds #055

Related Posts

38 Comments. Leave new

  • INNER JOIN! As you said, I feel that it is cleaner, definitively eliminates confusion on which join is really meant. Thanks for agreeing with me :)

    Reply
  • Yes inner join and join are same performance wise and implementation wise

    Reply
  • Hey!
    I was shocked by seeing the title of the post, first thought came to my mind was “aren’t they both the same?” and again I thought let’s see if there were any differences. All in all, a moment of anxiety!
    well, I always use the term “INNER JOIN” rather then “JOIN” in real time
    tbh, I use JOIN in my localhost when i quickly want to try out something. :)
    Best Regards
    Cheers

    Reply
  • I use mostly inner join mostly for readability

    Reply
  • Sanjay Monpara
    July 16, 2013 10:40 am

    I am using INNER JOIN because it is ANSI standard syntax & easy to migrate database from sql server to another database (oracle, mysql etc…)

    Reply
  • Renato Siqueira
    July 16, 2013 10:41 am

    INNER JOIN: because it’s ANSI-92 standard, and it’s a good practice after all (to identify the type of joins, avoid syntax errors too, etc).
    []’s

    Reply
  • Hi pinal,

    Here we go, i always opt to use inner join instead of join because from the very beginning i learnt as such and got used to the same.More over it is more readable and less prone to misunderstandings.So my opinion is to use inner join instead of join syntax.

    Thanks,
    Shan

    Reply
  • kalyanasundaram
    July 16, 2013 11:55 am

    Hi,
    I am mostly using INNER JOIN because it only gives clear variation for analyse joins using between tables.

    Normally, people know different types of joins. Simply they check this query is used Inner join or Outer join.

    Reply
  • Every time i use Inner Join, as it is easily readable.

    Reply
  • I use inner join Bcz it’s same as you told no confusion and looks also good as best practice.
    join looks some thing incomplete in query. inner join easy to understand.

    Reply
  • I use Inner Join because of constant use of right, left joins functions. As you say, it is more clean to my code.

    Reply
  • Madivalappa patil
    July 16, 2013 5:26 pm

    ACID stands for A-Automicity , C- Consistency ,I-Isolation and D- Durability

    Reply
  • IMO: INNER JOIN bcs. it’s in opposite to OUTER JOIN

    Reply
  • I usually just use JOIN (looks like I’m in the minority :)); I’ll use INNER JOIN when I am also doing at least one other type of join in the statement.

    Reply
  • I regularly used to write queries using join instead of inner join. I know there is no difference between them. From now onwards I will set my mind to use Inner join instead of join.

    Reply
  • I use inner join for readability and also simple to avoid any errors and trouble shooting while moving from one database to another also it is fun to use “INNER JOIN’

    Reply
  • I always write as ‘INNER JOIN’.coz its easy to understand for other devlopers

    Reply
  • I don’t think there is such a person that prefers JOIN instead of INNER JOIN, at all!

    Reply
  • its better to use Inner Join instead of join . join is not defining the exact meaning it may be cause of confusion so try to use Inner join as a professional

    Reply
  • I use JOIN. I avoid unnecessary typing. The same thing goes for OUTER JOINS. I use LEFT JOIN. Why would I type optional unnecessary code? I don’t buy that it makes your code more readable. Can anybody honestly say that if they came across the code: select * from table1 join table2 on table1.id = table2.id that they would be confused or that it would be hard to understand? I don’t get it.

    I understand it’s an ANSI standard, but It doesn’t make sense to code for all SQL platforms. We are talking about SQL Server, not all platforms. As far as portability is concerned… how often is a company going to completely change their SQL Servers to use Oracle or MySQL??

    Reply
    • You shouldn’t be writing code to save yourself typing. You should write code that is easier to read. Why leave ambiguity to save yourself a few keystrokes. I can honestly say that if you just specify “JOIN” and not “INNER JOIN” that it’s not obvious what it means. This is probably more true if you jump from Oracle to SQL Server to mySQL a lot or if you are a beginner. In the end, being ambiguous for the sake of a few keystrokes is usually not a good idea. In fact, I’m troubleshooting code right now where a jr programmer used JOIN and meant for it to be an outer join. When reading his code, I can’t be sure what he meant. I know what it is doing, but his intention isn’t clear. If it said INNER JOIN, his intention, at least, would be clear.

      Reply

Leave a Reply