Programming, SQL Database

Difference Between ON And WHERE Condition In Join

follow link Usually, while you use joins with ON condition and some time as WHERE condition but what is the exact use of both? There is not any difference between ON and WHERE in INNER JOIN so you can see the actual difference in LEFT JOIN or RIGHT JOIN.
To know the difference between ON and WHERE I tried with some simple examples.

follow link I created two tables Employee and Department as below with sample data and make the relation between them so easily to join both the tables.

http://www.newmen.eu/mysoroj/viosa/564 SELECT * FROM Department

http://chennaitrekkers.org/feeds/3619951216882988660/comments/default DepartmentSelect_techzigg

http://salsiando.com/finelit/1349 SELECT * FROM Employee

Employee_Select_techzigg

imdur 30 mg india rencontre gratuit pour les moins de 18 ans Scenario 1
Get all employees with their associated department either assigned or not:

clomid 250mg SELECT * FROM Employee E LEFT JOIN Department D ON E.DeptID = D.DeptID

cheap azulfidine side SimpleLeftJoin_techzigg
Here you can see LEFT JOIN used to get all employees data with their assigned department.

exemple de pseudo site de rencontre Scenario 2
Get all Employees list with associated departments A and B information only:

SELECT * FROM Employee E
LEFT JOIN Department D ON E.DeptID = D.DeptID
AND (D.Department ='A' OR D.Department = 'B')

leftjoin_ON_techzigg

Here you can see ON condition apply for Department table during LEFT JOIN so only department A and B data mapped to Employee data. You can also see here the condition in ON apply as 1-1 mapping and use to apply filtering row by row. Even you can also easily see on right side DeptID column with only display A or B data. So https://www.mccarthyarchitecture.com/indigose/9390 this is an example of use ON condition in LEFT JOIN.

http://www.backclinicinc.com/?jixer=puntate-treding-binario&eda=fe Scenario 3
Get only those employees data who have their department A or B:

SELECT * FROM Employee E
LEFT JOIN Department D ON E.DeptID = D.DeptID
WHERE (D.Department ='A' OR D.Department = 'B')

Employee_WhereCondition_techzigg

Here you can see WHERE clause applied as overall data filter so the only filter out those Employees data who have Department A or B. So you can use WHERE clause only when you require applying the condition on the final result of all joins between tables. So source site this is an example of use WHERE in LEFT JOIN.

You can use the same way to apply for to get departments with associated employees with same fundamentals to apply above scenarios and even you can use in RIGHT JOIN also but to use in INNER JOIN there will be the same result to apply condition in ON or WHERE. If you apply the same condition to both the places in ON and WHERE then ON will be applied as row by row and after that result given to WHERE to get the final filtered result by applying the condition.

2 thoughts on “Difference Between ON And WHERE Condition In Join

Leave a Reply

Your email address will not be published. Required fields are marked *