SQL Using with PostgreSQL
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.
- 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.
id SERIAL PRIMARY KEY,
Now let’s add some products:
('Almond Milk', 2.99, 1),
('Soy Milk', 2.49, 1),
('Whole Milk', 3.19, 1),
('Corn Chips', 1.99, 2),
('Tortilla Chips', 1.99, 2)
Here is our table:
id | name | price | department_id
1 | Almond Milk | 2.99 | 1
2 | Soy Milk | 2.49 | 1
3 | Whole Milk | 3.19 | 1
4 | Corn Chips | 1.99 | 2
5 | Tortilla Chips | 1.99 | 2
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:
department_id SERIAL PRIMARY KEY,
Now let’s add some data to it:
(1, 'Dairy', 'James'),
(2, 'Chips', 'Kathy');
Let’s select the data to make sure we’ve inserted the data correctly:
department_id | name | manager
1 | Dairy | James
2 | Chips | Kathy
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
department_id column to the
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:
1 | 1 | Almond Milk | 2.99 | Dairy | James
1 | 2 | Soy Milk | 2.49 | Dairy | James
1 | 3 | Whole Milk | 3.19 | Dairy | James
2 | 4 | Corn Chips | 1.99 | Chips | Kathy
2 | 5 | Tortilla Chips | 1.99 | Chips | Kathy
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
d instead of
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
departments used the field
name which was a little confusing.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started