In the last example, I do not understand why you are combining a CROSS JOIN and a JOIN… Couldn’t you just use a CROSS JOIN to connect all three tables?
I’m referring to the following query, as shown in the video:
employees e ON dm.emp_no = e.emp_no
d.dept_no <> dm.dept_no
ORDER BY dm.emp_no , d.dept_no;
Another great observation from your side!
Well, since in our database, ‘dept_manager’ data and ’employees’ data are matched perfectly, both versions of this query (i.e. yours and ours) deliver the same output.
However, if this was not the case, we would have preferred to use
JOIN since we would have not wanted our output to ‘explode’ in case there was more information in the ’employees’ table about each manager, as stored in the ‘dept_manager’ table. The latter could be obtained by using a
CROSS JOIN only.
So, in theoretical (and not practical) terms,
JOIN is the more secure choice.
However, once again, in our example, using any of the two delivers the correct outcome.