Thanks for the quick examples (here and elsewhere).
I think you should expand on what LEFT Vs RIGHT JOIN mean (ie. the table specified first is the 'left' table).
For CROSS JOIN I tend to think of it as an "EVERYTHING JOIN" and it can generate a MASSIVE amount of data and is generally bad. If you CROSS JOIN two tables with 1,000 rows each you get 1,000,000 rows.
There are practical uses for CROSS JOIN but they are few and far between. For example:
"I will generate a list of all reps linked to all products and you can then cross out the ones you don't want" has been quite common sort of thing.
I was surprised to see UNION discussed here but … yep it is a join or sorts. However, I think it is a whole new topic all on it's own.
Further topics? Sub-query? Co-related Subquery - and why is can be SOOO bad and should be avoided at all costs with anything other than small datasets!
Thanks for all your comments, I appreciate the feedback.
You’re right, I could have given more of an explanation as to what I meant by LEFT and RIGHT. The image doesn’t speak for itself so it could have been clearer.
The Cross join is more of a warning than anything else. As you say, things can get out of hand very quickly.
I included Unions at a very high level to show that you can ‘join’ tables both horizontally and vertically but agree it is a blog post on its own.
Thank you for suggestions too, I’ll be posting a few more data blog posts before the year is out!
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.