CROSS JOIN and JOIN together
Hello!
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:
SELECT
e.*, d.*
FROM
departments d
CROSS JOIN
dept_manager dm
JOIN
employees e ON dm.emp_no = e.emp_no
WHERE
d.dept_no <> dm.dept_no
ORDER BY dm.emp_no , d.dept_no;
Thanks!
Pablo
1 answers ( 0 marked as helpful)
Hi Dulce!
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.
Good observation!
Best,
365 Team