What’s the difference between using Inner Join or Where relationships

Asked

Viewed 5,509 times

8

Good morning, I am with the following doubt, I work in a company where people bother when it comes to using inner join for the sake of efficiency and database processing, I would like to know if the relationship between tables done in the where is really "better" and why I’ve always heard that Ner was better for letting the select cleaner and organized. Of course whether it occurs is not specifically for a bd server, or applies to all?

sql

3 answers

12


They are wrong. The notation inner join was introduced in the SQL92 version of the ANSI SQL standard. All major databases adopt the SQL92 join syntax. Since most servers were already on the market before the new standard, they also include the old merge form where, but it is preferable to use the new standard for the following reasons:

  • Junction conditions and filter conditions are separated into two different clauses (the subclavian on and the clause where, respectively), making the query easier to understand and maintain.
  • The join conditions for each pair of tables are contained in their own clauses on, making it less likely that part of a junction will be omitted by mistake.
  • Queries that use the SQL92 join syntax are portable between database servers, while the old syntax where is slightly different between different servers.

How can see here, at the end of everything in the same, so it is better to opt for the inner join

  • Thanks for the reply, it helped a lot!

3

In terms of results, there will be no difference. On performance, the INNER junction condition has a discrete gain compared to WHERE, because it is interpreted before.

Order of interpretation: 1 - FROM; 2 - ON; 3 - INNER; 4 - WHERE; 5 - SELECT;

But there are two factors that make the use of INNER to be advised: Standardization and flexibility.

The ANSI 92 standard establishes the use of Inners junction operators. With respect to flexibility, imagine that you change the business rule of your system and you need to change INNER by OUTER, using the WHERE clause, this can generate a little headache. Already with INNER, just change a few words.

2

Database servers provide resources for you to analyze how they will perform the query. Examples:

These features show what the execution plan of a particular query is and help you understand if something actually implies performance. In MS Sql Server, for example, you will find that the execution plan is identical to INNER JOIN explicit (using the ON clause) or implicit (determining the links in the WHERE clause).

Before guiding the way of coding for performance, it is important to learn how to use these resources and to know the behavior of the bank in fact, so as not to be hindered by myths and who knows even help bring down these myths.

How to the pros and cons, having been the performance already excluded:

  • As already quoted in another answer: INNER JOIN explicit can be converted to OUTER JOIN with less work.

  • As already quoted in another answer: INNER JOIN explicit is industry standard and therefore compatible among Dbms.

  • As for readability, it depends on the culture of the team. Your colleagues probably consider implicit INNER JOIN more cool.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.