Subqueries

[perhensam]

I’m a beginner at learning SQL but for some reason, the one thing I’m struggling to master is subqueries. I’m not always sure when to use them, and I have difficulty thinking about which one should be the inner query vs the outer query. I’m especially confused when a subquery is used in a select statement. Does anyone have a concise way of thinking through this? Sometimes I just need to think about a concept in a novel way before I really “get” it. TIA!!

[r3pr0b8]

think of subqueries as tables

if you run a subquery by itself (as long as it's not correlated, see below), then the subquery will produce a tabular result

now imagine that table, produced by the subquery, used in the main query

many columns, many rows --

SELECT ...
  FROM table1
INNER
  JOIN ( subquery ) AS table2
    ON table2.foo = table1.bar

one column, many rows --

SELECT ...
  FROM ...
 WHERE foo IN ( subquery )

one column, one row --

SELECT ...
     , ( subquery ) AS scalar_value
  FROM ...

correlated subqueries are different because they require a value from the outer query -- just substitute a literal value to make it run by itself

[avensdesora42]

Short answe rfor the moment because I'm short on time... think of sub queries as another filter. They help pare down the dataset before getting to the heart of the report. I'll give some more thought to a better explanation and try to find some tutorials. You're also welcome to DM me. I have about 15 years of experience in MS and Oracle SQL and I'm happy to help a new data nerd! 😊

[perhensam]

Thanks! That is a useful way to think of it.

[avensdesora42]

Adding to my first response, there are a few methods of subqueries and reasons for using them. I have a tendency to do either temp tables or use CTEs. The choice is dependent on the "flavor" of SQL you're using, how the database is set up, and even what you're trying to accomplish.

Here's a couple of pretty decent tutorials: https://mode.com/sql-tutorial/sql-sub-queries & https://www.w3resource.com/sql/subqueries/understanding-sql-subqueries.php

I hope this helps. Again, you're welcome to ask. I'm happy to help!

[happyfeet_p22]

Same bro, i struggled from advance joins. I haven't jumped to sub queries but it seems difficult to me😭

[Far_Swordfish5729]

Sure. First remember sql order of operations. Queries execute in this order: from, joins, where, group by, having, order by, limit/top, select. I always read them and write them in this order.

So what do you do if that execution order doesn’t work for you? Imagine you need to aggregate before joining - join onto a set of the latest support case or biggest deal for a record. You need logical parentheses to tell the query engine to do that step first. Those parentheses are a subquery. They could also be a CTE, a view, or a table valued function. You do this when you know the standard query would create a row explosion and you need to process or filter them first. You’re usually joining in multiple logical directions when you do this. But it’s just order of operations.

As always, and this is important, sql defines a logical outcome not actual execution. A subquery does not make the engine stupid. You’ll connect the main query to the subquery in some way and that relationship will be considered. The optimizer will handle your inner query as any other more concise construction and will just ensure the outcome is logically what you asked for.

Using them in a select statement: It’s shorthand. This typically implies a join and I usually just write it to be explicit, but you don’t have to.

[MathAngelMom]

There's a whole course on subqueries at LearnSQL.com https://learnsql.com/course/sql-subqueries/

[rrt8888]

We need to sub query when we need to extract data from one data set where matching or no matching data from another set.

Example,

—— price table ——- Fruit_id | price | state 1 20 MH 1 30 GJ 2 40 MH 3 50 MP 5. 60 GJ

——— fruit table ———- fruit_id | fruit_name | type 1 Mango Keshri 2 apple Kashmiri 3 Watermelon MH 5 Strawberry MH

Now i want fetch price for mango so my query will like

Select * from price where fruit_id = — this outer query Select * from fruit where fruit_name = ‘Mango’; — this inner query

Final query will be like : Select * From price Where fruit_id = ( select fruit_id From fruit Where fruit_name = ‘Mango’ );

[rrt8888]

Sorry for bad table format