Using Outer joins in SQL

Question

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

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.

score 1 · Accepted Answer · answered Jan 13 '21 at 03:49

1

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.

answered Jan 13 '21 at 03:49

Jack Daus

66
3

Thanks, but is left outer join the same than left join? – Evolution1980 Jan 13 '21 at 03:56
Yep, LEFT OUTER JOIN and LEFT JOIN are the same thing. See https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server – Jack Daus Jan 13 '21 at 04:02

Using Outer joins in SQL

Sample output:

1 Answers1