Menu
I am working on rewriting some poorly written SQL queries and they are over-utilizing sub-queries. I am looking for best-practices regarding the use of sub-queries.
Any help would be appreciated.
919
If you need to combine related information from different rows within a table, then you can join the table with itself. Use subqueries when the result that you want requires more than one query and each subquery provides a subset of the table involved in the query.
A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
Joins versus Subqueries. Joins and subqueries both combine data into a single result using either . They share many similarities and differences. Once difference to notice is Subqueries return either scalar (single) values or a row set; whereas, joins return rows.
For a join, we would be required to fetch the whole table from each site and create a large table from which the filtering will occur, hence more time will be required. So for distributed databases, nested queries are better.
Subqueries are usually fine unless they are dependent subqueries (also known as correlated subqueries). If you are only using independent subqueries and they are using appropriate indexes then they should run quickly. If you have a dependent subquery you might run into performance problems because a dependent subquery typically needs to be run once for each row in the outer query. So if your outer query has 1000 rows, the subquery will be run 1000 times. On the other hand an independent subquery typically only needs to be evaluated once.
If you're not sure what is meant by a subquery being dependent or independent here's a rule of thumb - if you can take the subquery, remove it from its context, run it, and get a result set then it's an independent subquery.
If you get a syntax error because it refers to some tables outside of the subquery then its a dependent subquery.
The general rule of course has a few exceptions. For example:
If performance is an issue then measure your specific queries and see what works best for you.
124
There is no silver bullet here. Each and every usage has to be independently assessed. There are some cases where correlated subqueries are plain inefficient, this one below is better written as a JOIN
select nickname, (select top 1 votedate from votes where user_id=u.id order by 1 desc) from users u On the other hand, EXISTS and NOT EXISTS queries will win out over JOINs.
select ... where NOT EXISTS (.....) Is normally faster than
select ... FROM A LEFT JOIN B where B.ID is null Yet even these generalizations can be untrue for any particular schema and data distribution.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
