What is the difference between ON C1=C2 AND C3=C4 and ON (C1=C2 AND C3=C4)?

1

Hello

I think SQL returns different results from the below queries:

JOIN T1 ON C1=C2 AND C3=C4
and
JOIN T1 ON (C1=C2 AND C3=C4)

Any idea what is the difference?

Also, by the way, how is the JOIN processed? Does it default to LEFT OUTER JOIN?

Thanks!

2

the way it works ..

2 concepts ..

first concept is precedence .. i mean which one first and which one second ..

the second concept is ( ) brackets .. this () is evaluated independantly
and the result is used as a single entity in some order of precedence

i can give examples if you dont understand

3

You've been asking lots of questions involving operator precedence. You may find this useful.

4

No difference - the () does not change operator precedence so the statements are the same. JOIN defaults to INNER JOIN which is why you really need to get in the habit of specifying the join type.

5

In this case, there is no difference. If OR's are present in the criteria, then the parentheses can make all the difference in the world.

Shifting gears a bit and with the understanding that it's a 100% personal preference, I don't specify INNER JOIN. I normally just use JOIN to make outer joins more painfully apparent because of indentation differences. I say "normally" because, if I do work for another shop, I'll follow whatever standards they have and very few have such a standard of specifying the use of INNER JOIN instead of just JOIN.

6

I always specify INNER JOIN - but that is personal preference - specifying JOIN works just fine as long as everyone knows it defaults to inner join. And from the OP's question that isn't the case here...

7

Thanks. I assume, at least, that 'JOIN', 'OUTER JOIN', 'INNER JOIN' always default to LEFT joins, right?

8

No... You really need to read the documentation about joins. OUTER JOIN doesn't work at all in SQL Server as syntax. You need to specify LEFT, RIGHT, or FULL.

From all the posts I've been watching you ask, you really need to start studying the documentation. That's not meant as a criticism. That's meant as cold, hard, and helpful advice to help you with your career whether it's for SQL Server or anything else.

1 Like
9

Totally agreed on both points. I'll also state that if someone doesn't actually know that JOIN means "INNER JOIN", I don't actually want them working in SQL. :rofl:

1 Like
10

Depends on the person - if I have an entry level person, knowing JOIN vs INNER JOIN is something that can be trained. At that point, using INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, etc... is what will be taught.

Once that person gets those concepts down - then we can look at removing optional portions and how that affects the formatting of the code. And why formatting the code is important...

1 Like
11

It takes less than 5 seconds to tell someone that JOIN and INNER JOIN mean the same thing. If they don't get that in those 5 seconds, then they're really the wrong person even if they're new.

I also recognize that it IS a personal preference and so there's no need to go back and change code that's already been written so long as it's readable and follows some decent form of formatting.

1 Like