Correlated vs Non-correlated subqueries in SQL

Question

I am working with a correlated and a non-correlated subquery in SQL and I am trying to get the same results with both types of queries. My issue is with my non-correlated subquery. The query runs but does not return any results. My correlated subquery does return results, like it should. I need help trying to figure out if my simple non-correlated subquery is written incorrectly. Any help is greatly appreciated. My queries are as follows:

--non-correlated subquery

SELECT *
FROM hr.bc_products p
WHERE p.sku NOT IN (SELECT ol.sku FROM hr.bc_orderlines ol);

--correlated subquery

SELECT * 
FROM hr.bc_products p
WHERE NOT EXISTS (SELECT ol.sku FROM hr.bc_orderlines ol WHERE ol.sku = p.sku);

fwiw, I'd tend to write this with an exclusion join: SELECT p.* FROM hr.bc_products p LEFT JOIN hr.bc_orderlines ol ON p.sku = ol.sku WHERE ol.sku IS NULL — Joel Coehoorn
– Joel Coehoorn, Commented Mar 25, 2014 at 2:37

Sonic · Accepted Answer · 2014-03-27 02:32:06Z

1

I finally figured out what the issue was. After looking through the results of each query I realized that nulls were affecting the outcome. I updated my non-correlated subquery by excluding nulls. My updated non-correlated subquery works and looks like this:

--non-correlated subquery

SELECT *
FROM hr.bc_products p
WHERE p.sku NOT IN (SELECT ol.sku FROM hr.bc_orderlines ol WHERE ol.sku IS NOT NULL);

Hopefully this will help someone avoid the problem in the future.

answered Mar 27, 2014 at 2:32

Sonic

134 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Correlated vs Non-correlated subqueries in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related