Login

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

Let's look at an example using our dummy testproducts table:

 testproduct_id |      product_name      | category_id
----------------+------------------------+-------------
              1 | Johns Fruit Cake       |           3
              2 | Marys Healthy Mix      |           9
              3 | Peters Scary Stuff     |          10
              4 | Jims Secret Recipe     |          11
              5 | Elisabeths Best Apples |          12
              6 | Janes Favorite Cheese  |           4
              7 | Billys Home Made Pizza |          13
              8 | Ellas Special Salmon   |           8
              9 | Roberts Rich Spaghetti |           5
            10 | Mias Popular Ice        |          14
(10 rows)

We will try to join the testproducts table with the categories table:

 category_id | category_name  |                       description
-------------+----------------+------------------------------------------------------------
           1 | Beverages      | Soft drinks, coffees, teas, beers, and ales
           2 | Condiments     | Sweet and savory sauces, relishes, spreads, and seasonings
           3 | Confections    | Desserts, candies, and sweet breads
           4 | Dairy Products | Cheeses
           5 | Grains/Cereals | Breads, crackers, pasta, and cereal
           6 | Meat/Poultry   | Prepared meats
           7 | Produce        | Dried fruit and bean curd
           8 | Seafood        | Seaweed and fish
(8 rows)

Notice that many of the products in testproducts have a category_id that does not match any of the categories in the categories table.

By using INNER JOIN we will not get the records where there is not a match, we will only get the records that matches both tables:

Example

Join testproducts to categories using the category_id column:

SELECT testproduct_id, product_name, category_name
FROM testproducts
INNER JOIN categories ON testproducts.category_id = categories.category_id;

Result

Only the records with a match in BOTH tables are returned:

 testproduct_id |      product_name      | category_name
----------------+------------------------+----------------
              1 | Johns Fruit Cake       | Confections
              6 | Janes Favorite Cheese  | Dairy Products
              8 | Ellas Special Salmon   | Seafood
              9 | Roberts Rich Spaghetti | Grains/Cereals
(4 rows)

Note: JOIN and INNER JOIN will give the same result.

INNER is the default join type for JOIN, so when you write JOIN the parser actually writes INNER JOIN.

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables:

SELECT * FROM orders
 customers
ON orders.customer_id = customers.customer_id;

Login
ADS CODE