You should NOT use the
, (comma) as a proxy for a
CROSS JOIN clause to join your tables. It provides
CROSS JOIN functionality, but at a cost - that of readability, clarity and explicitness - this latter word meaning the quality of being clear and exact (wasn't sure it existed)!
As for your question about "
why the comma" (in the past, but not any more - or, at least, it has fallen out of favour), see the discussion below!
Software spends way more time in maintenance mode than in development more, so it's important that your software be easily read and maintained!
CROSS JOIN can be logically thought of as this (nice image from here):
Another (again helpful) way of looking at this is (same link):
CROSS JOINs have useful, every-day, practical applications!
Consider the following three queries (see the fiddle here):
SELECT * FROM meal, drink;
Result (same for all queries):
Omlette Orange Juice
Fried Egg Coffee
Fried Egg Tea
Fried Egg Orange Juice
Sausage Orange Juice
So, for a dev looking at that query, they might say: "How many fields are there?", "What do these fields do?" or "What is the performance impact of this query?". We are precisely 0% of the way to being explicit!
CROSS JOIN drink;
Slightly clearer - at least we can see from 10,000m that this is a
CROSS JOIN because the term is there in black and white! So, we're ~ 33% of the way to being explicit!
Finally, consider this one (best):
SELECT m.mname, d.dname
FROM meal m
CROSS JOIN drink d;
So, now, we're as explicit as we can be. There's an alternative to this one and that is (sligthly reduces clarity - but is clearer than comma!):
Query 4 (also ran...):
SELECT m.mname, d.dname
FROM meal m
JOIN drink d ON TRUE; -- or 1 = 1;
I would urge you to take a look at this excellent article (Explicit Coding Discipline - the BIG ONE is no. 3):
- Why explicit matters
- Explicit naming
- Avoid tricks in favor of explicit code
- Conclusion (quote below):
The author wraps it up with:
The explicit coding discipline favors clear and explicit expression of
intent in the code.
It suggests writing meaningful names for variables, functions, classes
and other constructions. It suggests avoiding tricky solutions in
favor of readings and clear intent.
Why the comma?
As for the comma - it's just a syntax, notational thing - probably, if one were designing SQL now, it wouldn't be the first choice (or any choice) as a way to
CROSS JOIN tables. The term
CONCAT JOIN (à la Python syntax...) might be a potential candidate (for an ab initio design) - but it just sounds weird now that we (SQL devs/DBAs) are used to
Also, the separator for various fields in a query is a comma:
SELECT t1.f1, t2.f2
ON t1.k = t2.fk
So, it possibly (before my time...) appeared logical to use it as a table separator also.
The most important thing is clarity of expression, and don't forget that SQL is considerably (~ 20 years) older than Python and that many languages have legacies from their past which might be better off being expunged - but just consider the hiatus going from Python 2 to Python 3 - that has (and continues to) caused major difficulties (for the record, I admire Guido van Rossum for his courage in making that change - broken code...).
The problem for SQL is that it is designed by committee and, at least from my experience, "
courage" is not a trait I would associate with committees! :-) The comma has fallen out of favour and is discouraged by many serious practitioners but it has not (unfortunately) been eliminated with arrival of ANSI
JOINs which are clearer.
p.s. welcome to the forum!