THIS IS WHY YOU SHOULD NOT USE WHERE CLAUSE IN YOUR JOIN QUERY
Understand the difference between Filtering and Joining
Before we start a quick introduction on Inner Joins and Where clause.
INNER JOINS:
The Keyword INNER JOIN(can be referred as simply JOIN too) selects records that have matching values in the both tables.It is an intersection operation between the tables.
WHERE:
The WHERE clause is used to filter the records from the tables and return the records that satisfies the given condition.
Let us take the popular employees database(specifically 2 tables employees and salary) to demonstrate how inner join and where clause differs in terms of speed and memory.
The target is to select the data of employees who’s salary is more than 10000 and there are several queries to return this data.
1)
This query initially joins the two tables employees and salary and then start to filter the records by the condition given in the WHERE clause so as expected this query takes time and also memory as it has to join both tables and then remove the records and we can understand this by viewing the query report.
2)
In this query instead of explicit join (i.e explicitly specifying the type of join) we have used the Implicit join method(i.e using where condition and does not specify the type of join) and only lacks when the conditions increases thus reducing the readability of the code but in other ways it works perfectly fine and better than the previous method specified in terms of fetching time.
3)
In this query we have specified both the conditions in the JOIN clause itself and this increases query execution time and reduces the Fetching time comparing to the first case.
Although the implicit join performs much better in case of inner joins what lacks is the readability of the code although some may not face any issues with the code readability, so if you are working on a project that involves different people it’s better to use Explicit join rather than implicit join.
SUMMARY:
Understand the differences between filtering and joining the table.
Always try to use the explicit join method to make your code more readable.
Different SQL languages may have different query plans based on filtering in the ON clause vs the WHERE clause, so test the performance on respective database.
