What is the difference between using JOIN/ON and just using WHERE?
For example, take your second query, and instead make it FROM employee e, employee m WHERE e.manager_id(+) = m.employee_id
I've never used the JOIN/ON syntax. Does it have an advantage?
Eddie Awad
· 3 years ago
Hi Rob,
The difference between using JOIN/ON and just using WHERE is that the first uses the ANSI/ISO SQL92 standard syntax, and the second uses the good old Oracle syntax.
I refer you to this post for some discussion about the difference between the two and the advantages/disadvantages of using the ANSI SQL syntax (make sure you read the comments).
By the way,
<pre>FROM employees e LEFT OUTER JOIN employees m ON e.manager_id = m.employee_id </pre>
translates to
<pre> FROM employees e, employees m WHERE e.manager_id = m.employee_id (+) </pre>
The (+) operator following m.employee_id means that you want to display a row from the m table, even though there exists no corresponding row in the e table, and that's what the LEFT OUTER JOIN above means.
yas
· 3 years ago
The (+) operator following m.employee_id means that you want to display a row from the m table, even though there exists no corresponding row in the e table
Eddie, i think it must be the other way around. This code gets all rows from e, not m. This (+) is usually confusing to many people, it makes the sql get all the rows from the table on the other side.
Nelson
· 3 years ago
Just a bit off topic. I clicked on the Spain's flag on top of the screen and the text it showed make no sense at all! The damn thing even translate the code so it is a bit futile to run that translation.
Eddie Awad
· 3 years ago
Yas, you're right, the corrected statement that describes
WHERE e.manager_id = m.employee_id (+)
is:
The (+) operator following m.employee_id means that you want to display a row from the e table, even though there exists no corresponding row in the m table.
Thanks for catching it.
Sukaina
· 2 years ago
As stated by Eddie: WHERE d1.department_id IN (10, 20, 30, 40) AND d2.department_id IN (10, 20, 30, 40); What if the department_ids are around 100 in number, would it not be a roblem to enter all department_ids in the query. Using Oracle I can solve it, however using ANSI do we have to use a subquery there as in Oracle or is there a seperate way. Please let me know.
All Comments
What is the difference between using JOIN/ON and just using WHERE?
For example, take your second query, and instead make it
FROM employee e, employee m WHERE e.manager_id(+) = m.employee_id
I've never used the JOIN/ON syntax. Does it have an advantage?
Hi Rob,
The difference between using JOIN/ON and just using WHERE is that the first uses the ANSI/ISO SQL92 standard syntax, and the second uses the good old Oracle syntax.
I refer you to this post for some discussion about the difference between the two and the advantages/disadvantages of using the ANSI SQL syntax (make sure you read the comments).
By the way,
<pre>
FROM employees e LEFT OUTER JOIN employees m
</pre>ON e.manager_id = m.employee_id
translates to
<pre>
FROM employees e, employees m
</pre>WHERE e.manager_id = m.employee_id (+)
The (+) operator following m.employee_id means that you want to display a row from the m table, even though there exists no corresponding row in the e table, and that's what the LEFT OUTER JOIN above means.
The (+) operator following m.employee_id means that you want to display a row from the m table, even though there exists no corresponding row in the e table
Eddie, i think it must be the other way around. This code gets all rows from e, not m. This (+) is usually confusing to many people, it makes the sql get all the rows from the table on the other side.
Just a bit off topic.
I clicked on the Spain's flag on top of the screen and the text it showed make no sense at all!
The damn thing even translate the code so it is a bit futile to run that translation.
Yas, you're right, the corrected statement that describes
WHERE e.manager_id = m.employee_id (+)
is:
The (+) operator following m.employee_id means that you want to display a row from the e table, even though there exists no corresponding row in the m table.
Thanks for catching it.
As stated by Eddie:
WHERE d1.department_id IN (10, 20, 30, 40)
AND d2.department_id IN (10, 20, 30, 40);
What if the department_ids are around 100 in number, would it not be a roblem to enter all department_ids in the query. Using Oracle I can solve it, however using ANSI do we have to use a subquery there as in Oracle or is there a seperate way. Please let me know.