CODEWITHSUNDEEP
sqlsubquerycorrelated-subquery
Sonic
Sonic

Reputation: 13

Correlated vs Non-correlated subqueries in SQL

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);

Upvotes: 0

Views: 950

Answers (1)

Sonic
Sonic

Reputation: 13

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.

Upvotes: 1

Related Questions