Over a million developers have joined DZone.

How USING Breaks Your SQL Queries

· Java Zone

What every Java engineer should know about microservices: Reactive Microservices Architecture.  Brought to you in partnership with Lightbend.

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

Microservices for Java, explained. Revitalize your legacy systems (and your career) with Reactive Microservices Architecture, a free O'Reilly book. Brought to you in partnership with Lightbend.


The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}