3 ms·
Most DBAs I know consider any comma join syntax difficult to maintain and difficult to debug because it's often difficult to tell the difference between join co
by da_chicken 8y ago
Most DBAs I know consider any comma join syntax difficult to maintain and difficult to debug because it's often difficult to tell the difference between join conditions and filter conditions. It's also very easy to mistakenly create a CROSS JOIN with comma join syntax, too.
The real pain comes when you try comparing the old vendor specific OUTER JOIN syntax for Oracle and SQL Server. I guarantee you'll love JOIN ... ON ... over comma joins.
Oracle:
SELECT *
FROM T1, T2
WHERE T1.PK1 = T2.FK1(+)
SQL Server:
SELECT *
FROM T1, T2
WHERE T1.PK1 *= T2.FK1
Note that the outer indicator goes on the opposite side. Oh, and, of course, if you flip the order of the fields around, you've got to remember to flip the (+) operator. Which of these three are identical:
SELECT *
FROM T1, T2
WHERE T1.PK1(+) = T2.FK1
SELECT *
FROM T1, T2
WHERE T2.FK1(+) = T1.PK1
SELECT *
FROM T1, T2
WHERE T2.FK1 = T1.PK1(+)
Now imagine you're joining 5 tables and want to reuse the JOIN syntax.
Yeah. Fuck that. I'll take this any day:
SELECT *
FROM T1
LEFT JOIN T2
ON T1.PK1 = T2.FK1
- laumars 8y agoI have genuinly written a lot of complex SQL for both MySQL and Oracle and honestly I do prefer Oracles syntax for joins. But I did spend several years writing PL/SQL before learning ANSI SQL so I guess it might just be a question of what you're used to?
- GFischer 8y agoMaybe on what you got started on. I started with T-SQL, then PL/SQL and now back to T-SQL, so you can guess which I prefer.
- da_chicken 8y agoI would just be aware that the prevailing opinion is that the ANSI syntax is considered better because it's considered clearer, more maintainable, and more functional. I was perfectly fine with comma joins until I had to interact with both SQL Server and Oracle. Then I found all kinds of hidden benefits. Like if I want to query the same set of tables, I can just copy the whole FROM clause and reuse it whole-hog. The logical separation is just nice, too. My tables know how they relate to each other. They know what type of join is being done. I don't have to tell the fields how they relate to each other. Even if I get my join conditions wrong, the relationships are correct. Even Oracle's own documentation[0] tells you to avoid the (+) syntax: > Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to [several restrictions], which do not apply to the FROM clause OUTER JOIN syntax[.] The doc itself lists all the issues, which is about 10 of them. None of these are opinions, either. They're actually technical limitations with (+), though they may not be ones that you encounter. The only advantage I know of for the (+) syntax is some uncommon issues with materialized view optimizations. [0]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/queri...
- stareatgoats 8y agoMe too. Joins FTW.