Swati Swati - 3 months ago 10
SQL Question

Alternative to using subqueries in SQL statements?

I have two tables:

TableA: (a temporary table)
ItemId (int)

TableB:
ItemId (int), ParentID (int)


I want to retrieve all items in Table A where the ParentID of any of the items in Table A doesn't exist as an ItemID. (i.e. I want to get the root of the items in TableA)

This query does what I want:

SELECT a.ItemID
FROM TableA a
INNER JOIN TableB b ON a.ItemId = b.ItemID
WHERE b.ParentID NOT IN ( SELECT * from TableA )


as does this one:

SELECT b.ItemID
FROM TableB b
WHERE b.ItemID IN ( SELECT * FROM TableA)
AND b.ParentID NOT IN ( SELECT * FROM TableA )


I am not satisfied with either of the queries, particularly because of the use of NOT IN/IN. Is there a way to do this without them? Perhaps a cleaner way that doesn't require subqueries?

Sample Data:

Table A
-------
2
3
5
6

Table B
--------
1 | NULL
2 | 1
3 | 1
4 | 3
5 | 3
6 | 3


Desired Result:

2
3


Thanks

Answer

Without subqueries:

SELECT ItemID
  FROM TableA
INTERSECT 
SELECT b.ItemID
  FROM TableB AS b
       LEFT OUTER JOIN TableA AS a
          ON b.ParentID = a.ItemID
 WHERE a.ItemID IS NULL;

...but is your fear of subqueries rational? :) I'd find this equivalent query easier to read and understand:

SELECT ItemID
  FROM TableA
INTERSECT 
SELECT ItemID
  FROM TableB
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM TableA AS a
                    WHERE a.ItemID = TableB.ParentID
                  );
Comments