Login

JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let's look at a selection from the products table:

 product_id |  product_name  | category_id
------------+----------------+-------------
         33 | Geitost        |           4
         34 | Sasquatch Ale  |           1
         35 | Steeleye Stout |           1
         36 | Inlagd Sill    |           8

Then, look at a selection from the categories table:

 category_id | category_name
-------------+----------------
           1 | Beverages
           2 | Condiments
           3 | Confections
           4 | Dairy Products

Notice that the category_id column in the products table refers to the category_id in the categories table. The relationship between the two tables above is the category_id column.

Then, we can create the following SQL statement (with a JOIN), that selects records that have matching values in both tables:

Example

Join products to categories using the category_id column:

SELECT product_id, product_name, category_name
FROM products
INNER JOIN categories ON products.category_id = categories.category_id;

If we pull out the same selection from products table above, we get this result:

Result

 product_id |  product_name  | category_name
------------+----------------+----------------
         33 | Geitost        | Dairy Products
         34 | Sasquatch Ale  | Beverages
         35 | Steeleye Stout | Beverages
         36 | Inlagd Sill    | Seafood

Different Types of Joins

Here are the different types of the Joins in PostgreSQL:

  • INNER JOIN: Returns records that have matching values in both tables
  • LEFT JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
  • FULL JOIN: Returns all records when there is a match in either left or right table

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Insert the missing parts in the JOIN clause to join the two tables orders and customers, using the customer_id field in both tables as the relationship:

SELECT * FROM orders
LEFT JOIN customers
 = ;

Login
ADS CODE