Good morning, I have the following doubt, I work in a company where the staff fills up when it comes to using inner join for efficiency and database processing, I wonder if the relationship between tables done no where is really "better" and because I've always heard that inner was better for leaving the select more neat and organized.
Of course if it occurs it is not specifically for a bd server, or does it apply to everyone?
What's the difference between using inner join or for relationships in where
7
asked by anonymous 23.12.2014 / 13:30
3 answers
11
They are wrong. The inner join notation 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 where join form, however it is preferable to use the new default for the following reasons:
- Splicing conditions and filter conditions are separated into two different clauses (the subclause
onand thewhereclause, respectively), making the query easier to understand and maintain. - The join conditions for each pair of tables are contained in their own
onclauses, making it less likely that part of a join is mistakenly omitted. - Queries that use SQL92 join syntax are portable between database servers, whereas the old
wheresyntax is slightly different between different servers.
How can see here , at the end of it all, by it is better to opt for inner join
23.12.2014 / 13:54
3
In terms of results, there will be no differences. On performance, the INNER join condition has a discrete gain compared to WHERE, because it has been 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 advisable: Standardization and flexibility.
The ANSI 92 standard establishes the use of INNERs join operators. With respect to flexibility, imagine changing the business rule of your system and you need to change the INNER by OUTER using the WHERE clause, this can generate a bit of a headache. Already with INNER, just change some words.
23.12.2014 / 14:08
2
Database servers provide resources for you to analyze how they will run the query. Examples:
These features show you the execution plan of a particular query and help you understand whether something actually implies performance. In MS Sql Server, for example, you will find that the execution plan is identical to explicit INNER JOIN (using the ON clause) or implicit (determining the WHERE clause bindings).
Before you figure out how to code for performance, it's important to learn how to use these features and really know the bank's behavior so that you do not get in the way of myths and maybe even help break down these myths.
How many pros and cons, with performance already ruled out:
-
As far as readability, this depends on the culture of the team. Your colleagues probably consider the INNER JOIN implicitly cooler.
23.12.2014 / 15:29