Re:Usql – Universal command-line interface for SQL databases
by laumars on Thursday June 14, 2018 @03:02PM
(#17314407)
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?
Re:Usql – Universal command-line interface for SQL databases
by da_chicken on Thursday June 14, 2018 @05:23PM
(#17315499)
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/queri...
Re:Usql – Universal command-line interface for SQL databases
by GFischer on Thursday June 14, 2018 @04:07PM
(#17314914)
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.
Re:Usql – Universal command-line interface for SQL databases
by stareatgoats on Thursday June 14, 2018 @04:03PM
(#17314879)
Me too. Joins FTW.
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:
SQL Server: 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: Now imagine you're joining 5 tables and want to reuse the JOIN syntax.Yeah. Fuck that. I'll take this any day: