The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

CROSS JOIN and JOIN together

CROSS JOIN and JOIN together

0
Votes
1
Answer

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 Answer

365 Team
0
Votes

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