CODEWITHSUNDEEP
sqlouter-join
Evolution1980
Evolution1980

Reputation: 21

Using Outer joins in SQL

by analyzing the following diagram, I have to make a query to list all the products (product) and their associated category (product_category) if they have one. Otherwise, display a value (null) for the category.

Display only the PRODUCT_NAME and CATEGORY_NAME columns in this order.

Sample output:

PRODUCT_NAME CATEGORY_NAME
ProForm 6.0 RT Fitness
Wilwood 260-11179 Automotive
HC-SR04 null

enter image description here

So this is my query:

SELECT PRODUCT.name as PRODUCT_NAME, PRODUCT_CATEGORY.name as CATEGORY_NAME
FROM PRODUCT 
   JOIN PRODUCT_CATEGORY ON PRODUCT.product_category_id = PRODUCT_CATEGORY.product_category_id

But I am not sure of my query, indeed I think I should use outer joins.

Upvotes: 0

Views: 470

Answers (1)

Jack Daus
Jack Daus

Reputation: 88

You almost got it. You will need to use a left outer join to get the results you are looking for:

SELECT PRODUCT.name as PRODUCT_NAME, PRODUCT_CATEGORY.name as CATEGORY_NAME
FROM PRODUCT LEFT JOIN PRODUCT_CATEGORY 
ON PRODUCT.product_category_id = PRODUCT_CATEGORY.product_category_id

The left outer join will return all records from the left hand table (PRODUCT). If there are no joined records from the right-hand table (PRODUCT_CATEGORY), then NULL will be returned for that all columns in that right-hand table for those records.

Upvotes: 1

Related Questions