Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How USING Breaks Your SQL Queries

DZone's Guide to

How USING Breaks Your SQL Queries

· Java Zone ·
Free Resource

Download Microservices for Java Developers: A hands-on introduction to frameworks and containers. Brought to you in partnership with Red Hat.

You should be using ON for your JOINS. That's what Marko Tiikkaja says, at least, based on certain scenarios in which the USING clause can break your queries. According to Tikkaja, USING works fine in many situations, but he poses a particular scenario - one which is not at all far-fetched - where a car/parts database joins a manufacturers table using a manufacturer ID. Fine at first, but then the problem starts:

Everything is working great, until some day someone thinks that you should also track the manufacturer for each car.  So you run the following DDL:

alter table cars
  add column manufacturerid integer
  references manufacturers;

.. and boom.  The query shown above that previously worked correctly won't work anymore.  Even worse, if it's in a view (as opposed to a function or SQL in the application), the view will continue to work, and you might not even know that it's broken until you try to restore a dump of the database.

In other words, Tikkaja argues, you should use ON. The extra second or two of typing is probably worth it. And, as Tikkaja puts it, "SQL is not for the lazy."

Download Building Reactive Microservices in Java: Asynchronous and Event-Based Application Design. Brought to you in partnership with Red Hat

Topics:

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}