differences between correlated vs non-correlated sub query and cross join

what are differences between correlated vs non-correlated sub query

when we use each one of them
what is moving range date and how correlated sub query useful there?

also when we use Cartesian product with cross join
please advise
LVL 7
gudii9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
1) The correlation is the difference.
2) When you need it.
3) I rarely do. My most happened use-case: data distribution over a invariant tally table.
slightwv (䄆 Netminder) Commented:
>>what are differences between correlated vs non-correlated sub query

One of the top links from Google:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534134100346876724

Correlated vs. non-correlated isn't to do with how Oracle Database processes the SQL. It's just whether or not you include a column from the outer query in the subquery.

A subquery is correlated if you have a column from one or more parent tables in the subquery. So:

select * from departments dep
where employee_id in (select employee_id from employees emp where emp.employee_id = dep.employee_id)


is correlated because dep.employee is in the nested query. Whereas:

select * from departments
where employee_id in (select employee_id from employees);


is uncorrelated because there is no reference to departments in the subquery.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> what are differences between correlated vs non-correlated sub query, when we use each one of them

Above link from Netminder can give you a quick start and we need to use it based upon the requirements.

>> what is moving range date and how correlated sub query useful there?

Here you go and there are several approaches to handle Moving Date values including Correlated subqueries..
https://looker.com/blog/time-series-data-moving-or-rolling-aggregates-in-sql

>> also when we use Cartesian product with cross join

When you require Cartesian product data something like 24 hour values multiplied by 7 day data to get 168 values..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.