SQL accessing child table data

December 14, 2021

I have a task in which I’m using Northwind database to select only these products which category name begins with ‘C’. Product details are in table Products while CategoryName is in table Categories and this is the only answer I came up with

SELECT P.*
FROM Products P, Categories C WHERE C.CategoryName LIKE 'C%' AND P.CategoryID = C.CategoryID

I was curious if I can do it without putting Categories inside FROM clause and connecting the tables. I’m pretty new to dbms and for me it seemed logic that I can access child table and use the values inside it smh, can you explain why it’s not possible?

>Solution :

You can use a Correlated Subquery to achieve this:

   SELECT *
   FROM Products p
   WHERE EXISTS (SELECT id FROM Categories c WHERE c.id = p.id)

This subquery is "Correlated" in that the SQL in the subquery references the query in which it’s contained. Very similar to a join, but we are down in the WHERE clause using the EXISTS condition.

EXISTS is nice since it doesn’t care what is returned by the subquery. It only cares that ANYTHING was returned by the subquery. So that subquery could also be: SELECT 1 FROM Categories WHERE c.id = p.id and this query would still work.