SQL JOIN - WHERE clause vs. ON clause

Does not matter for inner joins

Matters for outer joins

a. WHERE clause: After joining. Records will be filtered after join has taken place.

b. ON clause - Before joining. Records (from right table) will be filtered before joining. This may end up as null in the result (since OUTER join).

4 years ago

In SQL, the 'WHERE' and 'ON' clause,are kind of Conditional Statemants, but the major difference between them are, the 'Where' Clause is used in Select/Update Statements for specifying the Conditions, whereas the 'ON' Clause is used in Joins, where it verifies or checks if the Records are Matched in the target and source tables, before the Tables are Joined
For Example: - 'WHERE'

SELECT  (asterisk) FROM employee WHERE employee_id=101

For Example: - 'ON'
There are two tables employee and employee_details, the matching columns are employee_id.

SELECT (asterisk) FROM employee 

INNER JOIN employee_details 

ON employee.employee_id = employee_details.employee_id

4 years ago

You can try these queries:

SELECT asterisk ()

FROM Orders

LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID

WHERE Orders.ID = 12345

and

SELECT asterisk ()

FROM Orders

LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 

    AND Orders.ID = 12345

4 years ago