In SQL I am trying to filter results based on an ID and wondering if there is any logical difference between
SELECT value FROM table1 JOIN table2 ON table1.id = table2.id WHERE table1.id = 1
and
SELECT value FROM table1 JOIN table2 ON table1.id = table2.id AND table1.id = 1
To me, it seems as if the logic is different though you will always get the same set of results but I wondered if there were any conditions under which you would get two different result sets (or would they always return the exact same two result sets)
In terms of readability though, especially in complex queries that have multiple joins, it is easier to spot join conditions when they are placed in the ON clause and filter conditions when they are placed in the WHERE clause.
Rows of the outer table that do not meet the condition specified in the On clause in the join are extended with null values for subordinate columns (columns of the subordinate table), whereas the Where clause filters the rows that actually were returned to the final output.
The subquery can be placed in the following SQL clauses they are WHERE clause, HAVING clause, FROM clause. Advantages Of Joins: The advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery.
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
The answer is NO difference, but:
I will always prefer to do the following.
ON
clausewhere
clauseThis makes the query more readable.
So I will use this query:
SELECT value FROM table1 INNER JOIN table2 ON table1.id = table2.id WHERE table1.id = 1
However when you are using OUTER JOIN'S
there is a big difference in keeping the filter in the ON
condition and Where
condition.
Logical Query Processing
The following list contains a general form of a query, along with step numbers assigned according to the order in which the different clauses are logically processed.
(5) SELECT (5-2) DISTINCT (5-3) TOP(<top_specification>) (5-1) <select_list> (1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate> | (1-A) <left_table> <apply_type> APPLY <right_table_expression> AS <alias> | (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias> | (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias> (2) WHERE <where_predicate> (3) GROUP BY <group_by_specification> (4) HAVING <having_predicate> (6) ORDER BY <order_by_list>;
Flow diagram logical query processing
(1) FROM: The FROM phase identifies the query’s source tables and processes table operators. Each table operator applies a series of sub phases. For example, the phases involved in a join are (1-J1) Cartesian product, (1-J2) ON Filter, (1-J3) Add Outer Rows. The FROM phase generates virtual table VT1.
(1-J1) Cartesian Product: This phase performs a Cartesian product (cross join) between the two tables involved in the table operator, generating VT1-J1.
it is referred from book "T-SQL Querying (Developer Reference)"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With