SQL Using with PostgreSQL

Introduction

The USING command can be quite hard to get a grasp on because it’s difference from other commands that accomplish similar functionality is quite subtle. We’ll spend some time discussing when you might use the USING command because it’s applied in very specific situations.

Prerequisites

• You should have PostgreSQL installed and running.
• A basic understanding of SQL joins will be beneficial to understanding this tutorial.

When to use the USING Clause

The USING clause is used when joining two tables together based on columns that have the exact same name and you’re checking for equality on those two columns. This is best demonstrated by an example so let’s create one!

Example of USING

First let’s say that we have a small grocery store and we are creating a database for it. First let’s create a table of products.

Now let’s add some products:

Here is our table:

Now we need to create the second table so we can demo the JOIN with the USING clause. To demo the using clause this second table departments will have to have a column name that is shared with the products table. We will make it the department_id column. Let’s create this table:

Now let’s add some data to it:

Let’s select the data to make sure we’ve inserted the data correctly:

Now let’s say we wanted needed to see what department each product was in. We’d need to join these two tables and match up the products department_id column to the departments department_id column. Notice that these two columns share the exact same name. Also notice that we are looking for equality between these columns. So we want to match up Almond Milk which has a deparment_id of 1, to the information in the departments table which has a department_id of 1.

This is the exact use case of the USING clause. Joining tables by equality on columns of the same name.

Let’s see the USING clause in action:

This is the result:

Clean up the query

Now let’s take a little time to polish the query we just made. Our intention was to find out the name of the department of each product so there’s a lot of extraneous information in our query which we can remove with a query like this:

Notice that we gave the tables aliases p and d instead of products and departments which made it easier to type. And we specified the exact fields we wanted this time in the SELECT part of the query. We also renamed the columns because both the products and departments used the field name which was a little confusing.

Conclusion

We have shown the use case for the USING clause in PostgreSQL and hope you have found the information you needed to apply to your specific problem or situation. Please don’t hesitate to reach out to us at Object Rocket with any database issues or questions.