Use Using

Join on field names

Category: SQL
Tags: SQL PostgreSQL

Intro🔗

In a previous post I described how to write SQL that would be readable and maintainable. It turns out that PostgreSQL has feature that can make it even better: USING.

Simple example🔗

Let's take an modified example of an earlier post.

file: create_table.sql

CREATE TABLE security (
    badge_number SERIAL NOT NULL PRIMARY KEY,
    --...
);

CREATE TABLE employee (
    employee_id SERIAL NOT NULL PRIMARY KEY,
    manager_id INTEGER,
    badge_number CHAR( 5 ) REFERENCES security( badge_number ),
    --...
);

CREATE TABLE hardware (
    employee_id SERIAL PRIMARY KEY,
    FOREIGN KEY (employee_id) REFERENCES employee( employee_id )
    --...
);

A simple query could be:

file: simple_query.sql

SELECT
    employee.*,
    hardware.*
FROM
    employee employee
    LEFT OUTER JOIN hardware hardware ON
        hardware.employee_id = employee.employee_id

Using to simplyfy it further:🔗

file: simplified_query.sql

SELECT
    employee.*,
    hardware.*
FROM
    employee employee
    LEFT OUTER JOIN hardware hardware USING( employee_id )

That works because both tables have the same field name.

Suggestion🔗

If you are the process of designing a new database, please concider to qualify the Joinable fields.

Instead of

file: instead.sql

CREATE TABLE something(
    id					SERIAL PRIMARY KEY,
    name				VARCHAR( 64 ) NOT NULL,
    ...
);
CREATE TABLE otherthing(
    id					SERIAL PRIMARY KEY,
    FOREIGN KEY( something_id )		REFERENCES something( something_id )
    ...
);

Use

file: do_this.sql

CREATE TABLE something(
    something_id			SERIAL PRIMARY KEY,
    name				VARCHAR( 64 ) NOT NULL,
    --...
);
CREATE TABLE otherthing(
    otherthing_id			SERIAL PRIMARY KEY,
    FOREIGN KEY( something_id )		REFERENCES something( something_id )
    --...
);

Advantages🔗

  1. You can use 'USING'
  2. If you are using a query tool (e.g. msqry32.exe), it automatically joins correctly 3 You still have the freedom to use the traditional syntax. You still would need to do that if you'd Join 'employee.employee_id = employee.manager_id'.

Disadvantages🔗

  • If you are using a ORM (SQLAlchemy, Doctrine, Hibernate, ..), you need to fiddle with the table meta settings.