CODEWITHSUNDEEP
sqloracle-database
Rajpreet Singh
Rajpreet Singh

Reputation: 11

Use where clause with IN Operator instead of join

How do I convert this SQL query from join to a subquery using the IN Operator. I was asked this question in an exam but I wasn't able to understand this clearly.

SELECT DISTINCT category_name
FROM categories c JOIN products p
ON c.category_id = p.category_id
ORDER BY category_name

an explanation of how it works will be great. Thanks

Upvotes: 1

Views: 161

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

When you do the conversion, you probably do not need the distinct. Presumably, in a table with categories, the names are already unique:

SELECT c.category_name
FROM categories c 
WHERE c.category_id IN (SELECT p.category_id FROM products p)
ORDER BY c.category_name

Upvotes: 0

Ousmane D.
Ousmane D.

Reputation: 56433

Using IN, the query becomes:

SELECT DISTINCT category_name
FROM categories c 
WHERE c.category_id IN 
(
    SELECT products.category_id 
    FROM products      
)
ORDER BY category_name

The IN operator is basically a shorthand for multiple OR conditions. So, in this case, for each record of categories, the subquery selects the products category_id's and checks if the categories.category_id is equal to any of the products.category_id's . if it's then that record will be included within the resultset and if not then it will be excluded from the resultset.

Upvotes: 2

Related Questions