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

Solarwinds
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)

Solarwinds
, ,
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

36 Comments. Leave new

  • i always using ‘ join ‘ now i understood what is the diff between join and inner join
    so because of professionalism i am going to use ‘inner join’…

    Reply
  • all joins names are same like left , right , inner join so not for confussion we use inner join rather then join yes they both are same

    Reply
  • Use Inner join only because when we use JOIN doesn’t know which join operation perform.

    Reply
  • I was shocked.! and I was curious to know what is the difference……! But got more shocked while reading the blog……..! I always use JOIN…….!

    Reply
  • I write “JOIN” because “INNER JOIN” has 5 more letters, then i spend more time writing “INNER JOIN”. XD

    Reply
  • I use JOIN! why? because “INNER JOIN” has 5 more letters, then it takes more time to be writen. :p

    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
  • Hello Sir,

    request you to help me for below query.
    For SQL Developer:
    We have a table movie with two columns naming Seat_id and Seat_Available ,
    We are three friends trying to select three continues seats for movie. Write a query.

    Seat_Id Seat_Available
    1 Y
    2 N
    3 Y
    4 Y
    5 N
    6 N
    7 Y
    8 N
    9 Y
    10 N
    11 Y
    12 Y
    13 Y
    14 Y
    15 N
    16 Y
    17 Y
    18 Y
    19 N

    Reply
    • contactsreekanth
      October 28, 2015 4:49 pm

      Ashish,

      You can use below queries for the same and pivot the result as you required.

      Answer1:
      SELECT M1.Seat_ID S1, M2.Seat_ID S2,M3.Seat_ID S3,M1.Seat_Avail S1A, M2.Seat_Avail S2A , M3.Seat_Avail S3A
      FROM Movie M1
      INNER JOIN Movie M2 ON M1.Seat_ID + 1 = M2.Seat_ID AND M1.Seat_Avail = ‘Y’ AND M2.Seat_Avail = ‘Y’
      INNER JOIN Movie M3 ON M1.Seat_ID + 2 = M3.Seat_ID AND M3.Seat_Avail = ‘Y’

      Answer2:
      ;WITH AvailableSeats AS
      (
      SELECT Seat_ID, Seat_Avail FROM Movie WHERE Seat_Avail = ‘Y’
      ),
      ConsecutiveSeats AS (
      SELECT A1.Seat_ID S1, A2.Seat_ID S2,A3.Seat_ID S3,A1.Seat_Avail S1A, A2.Seat_Avail S2A , A3.Seat_Avail S3A
      FROM AvailableSeats A1
      INNER JOIN AvailableSeats A2 ON A1.Seat_ID + 1 = A2.Seat_ID
      INNER JOIN AvailableSeats A3 ON A1.Seat_ID + 2 = A3.Seat_ID
      )
      SELECT * FROM ConsecutiveSeats

      Reply
  • contactsreekanth
    October 28, 2015 4:56 pm

    SELECT M1.Seat_ID S1, M2.Seat_ID S2,M3.Seat_ID S3,M1.Seat_Avail S1A, M2.Seat_Avail S2A , M3.Seat_Avail S3A
    FROM Movie M1
    INNER JOIN Movie M2 ON M1.Seat_ID + 1 = M2.Seat_ID AND M1.Seat_Avail = ‘Y’ AND M2.Seat_Avail = ‘Y’
    INNER JOIN Movie M3 ON M1.Seat_ID + 2 = M3.Seat_ID AND M3.Seat_Avail = ‘Y’

    If you want you can pivot the result as required.

    Reply
  • contactsreekanth
    October 28, 2015 5:07 pm

    you can use either of below queries and pivot it if required.

    SELECT M1.Seat_ID S1, M2.Seat_ID S2,M3.Seat_ID S3,M1.Seat_Avail S1A, M2.Seat_Avail S2A , M3.Seat_Avail S3A
    FROM Movie M1
    INNER JOIN Movie M2 ON M1.Seat_ID + 1 = M2.Seat_ID AND M1.Seat_Avail = ‘Y’ AND M2.Seat_Avail = ‘Y’
    INNER JOIN Movie M3 ON M1.Seat_ID + 2 = M3.Seat_ID AND M3.Seat_Avail = ‘Y’

    ;WITH AvailableSeats AS
    (
    SELECT Seat_ID, Seat_Avail FROM Movie WHERE Seat_Avail = ‘Y’
    ),
    ConsecutiveSeats AS (
    SELECT A1.Seat_ID S1, A2.Seat_ID S2,A3.Seat_ID S3,A1.Seat_Avail S1A, A2.Seat_Avail S2A , A3.Seat_Avail S3A
    FROM AvailableSeats A1
    INNER JOIN AvailableSeats A2 ON A1.Seat_ID + 1 = A2.Seat_ID
    INNER JOIN AvailableSeats A3 ON A1.Seat_ID + 2 = A3.Seat_ID
    )
    SELECT * FROM ConsecutiveSeats

    Reply
  • Yes, but it’s not ambiguous. It’s the same thing. It’s clear to anyone who knows the language. You’re just expressing a preference.

    Reply

Leave a Reply

Menu