2 ms·

I 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

I 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 ago
Maybe 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 ago
I 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...

GFischer 8y ago

Maybe 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 ago

I 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...