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

Just released, a free O’Reilly book on Reactive Microsystems: The Evolution of Microservices at Scale. 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."

Strategies and techniques for building scalable and resilient microservices to refactor a monolithic application step-by-step, a free O'Reilly book. Brought to you in partnership with Lightbend.

Topics:

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}