INNER JOIN and JOIN express the same inner join in this T-SQL syntax. I prefer spelling out the join type for readability.
This followed a reader discussion about the two not-equal operators. Developers were using both join spellings, so I asked whether the shorter form changed their meaning.
DECLARE @Table1 TABLE (Col1 int);
DECLARE @Table2 TABLE (Col1 int);
INSERT INTO @Table1 VALUES (1),(2);
INSERT INTO @Table2 VALUES (2),(3);
SELECT t1.Col1 AS left_value, t2.Col1 AS right_value
FROM @Table1 AS t1 INNER JOIN @Table2 AS t2 ON t1.Col1 = t2.Col1;
SELECT t1.Col1 AS left_value, t2.Col1 AS right_value
FROM @Table1 AS t1 JOIN @Table2 AS t2 ON t1.Col1 = t2.Col1;INNER JOIN and JOIN use the same matching rule
Both statements match the value 2 from the two input tables. This example differs only in the optional INNER keyword. It does not benchmark all possible join expressions or make a promise about unrelated text changes.
I prefer INNER JOIN because it names the join type clearly, especially alongside LEFT, RIGHT, or FULL joins. That is a readability preference, not a requirement for better performance.
Which spelling do you use, and what makes it clearer for your team? The earlier operator discussion remains linked below.
Read the result before changing the query
The left input has values 1 and 2. The right input has values 2 and 3. The equality condition finds only one matching pair, so each statement returns one row with 2 in both result columns. Neither spelling preserves the unmatched 1 or 3. An outer join would answer a different question.
The same rule also explains why adding duplicate matching values can produce more rows. A join returns matching row combinations; it does not automatically remove duplicate values. If an application expects one result per customer or order, verify the relationship and the condition rather than assuming that the shorter spelling changes the result.
When I review a query, I keep its inputs and condition fixed while comparing these two spellings. That separates a readability choice from a change to the requested data. A later change to the join type or predicate deserves its own result check.
Reference: Microsoft’s SQL Server joins documentation.
Related reading
The optional INNER keyword is not a performance hint, it is an explicit spelling of the join type.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
38 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’…
Good answer!
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
Use Inner join only because when we use JOIN doesn’t know which join operation perform.
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…….!
I write “JOIN” because “INNER JOIN” has 5 more letters, then i spend more time writing “INNER JOIN”. XD
I use JOIN! why? because “INNER JOIN” has 5 more letters, then it takes more time to be writen. :p
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.
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
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
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.
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
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.
good one
relatable