SQL Subqueries: Databases for Developers #11

In SQL you can base queries on the results of other queries.
This video explains the various types of subquery and their uses, covering:
- Inline views
- IN & EXISTS
- Common table expressions/subquery factoring/with clause
- Scalar subqueries
Take the course; Databases for Developers: Next Level blogs.oracle.com/academy/take...
Further reading:
About Queries and Subqueries: docs.oracle.com/en/database/o...
Using Subqueries: docs.oracle.com/en/database/o...
Need help with SQL? Ask us over on AskTOM: asktom.oracle.com
Twitter: / chrisrsaxon
Daily SQL Twitter tips: / sqldaily
All Things SQL blog: blogs.oracle.com/sql/
============================
The Magic of SQL with Chris Saxon
Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.

[@tjsahu]

Thank you for the wonderful lessons, really needed them to brush the basics

[@artemivanov2141]

Best chanel ever

[@NewYork0110J]

Chris -- so not exists and exists are generally correlated queries depending on the where clause condition in inner query. Aren't correlated queries expensive. What are the better options in those cases.

[@TheMagicofSQL]

Well they (NOT) EXISTS doesn't have to be a correlated query, but the query will almost be wrong if it's uncorrelated!
They're not necessarily "expensive" - in many cases the optimizer can transform these into a join.

[@NewYork0110J]

@@TheMagicofSQLThanks for Answering. How do we know if optimizer transform into the join. If i am working on large data set which is say 100k. this means this query may get executed 100K times. Was wondering should i write join condition myself to avoid correlated query

[@TheMagicofSQL]

Look at the plan for the query - that will tell you how the database is combining the tables. Check my other videos if you want more information on how to do this.
But in general with (NOT) EXISTS the database will either
Use a hash join (anti) which means it reads each table once, NOT 100k times
If there's a suitable index on the table in the subquery, it might do 100k lookups of this; on modern hardware this will generally still be fast enough

[@IMdAbdulquadirKhan]

chirs how did you started with data engineering and sql?

[@TheMagicofSQL]

My first job was as a PL/SQL developer for a small software house many years ago

[@IMdAbdulquadirKhan]

@@TheMagicofSQL you are really kind chirs not many people reply to everyone.

[@TheMagicofSQL]

I like to help :)

[@saipavankumar4498]

Sir your way of teaching is so nice but If u reduce the speed of English speaking then it will be nice for us

[@TheMagicofSQL]

Thanks!
I'll bear speaking speed in mind for future videos; note that CZcams enables you to slow it down yourself (Settings cog -> Playback Speed -> ...)

[@anujkumaryadav3969]

@@TheMagicofSQL Don't Worry Sir, It's not at all fast. Best of luck Go ahead sir.

[@r.r.r2367]

The background music is annoying.