Programming, SQL Database

Difference Between ON And WHERE Condition In Join

follow site 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 url 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.

hangi forex programı SELECT * FROM Department

http://www.romagnamotorsport.it/?binarnewe=opzioni-binarie-corso-formazione&f14=3e DepartmentSelect_techzigg

source site SELECT * FROM Employee

Employee_Select_techzigg

http://missionnorman.org/emiios/3962 Scenario 1
Get all employees with their associated department either assigned or not:

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

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

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 http://syaden.net/?giniefr=site-de-rencontres-chic&4f5=ae this is an example of use ON condition in LEFT JOIN.

http://feveda.com.ve/mefistofel/2325 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 http://documentalqueridowatson.es/pizdyhov/165 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 *