We can build a JOIN structure and obtain the same result. Look at the query with a JOIN :. In this query, we connect the two tables product and sale with a JOIN operator. In the JOIN condition, the records from the product table are linked to the records from the sale table through the product IDs. In this case, the subquery returns to the outer query a list of values. The outer query selects the names and the costs of the products; it then filters to the records whose product IDs belong on the list returned by the subquery.
There are more products in the product table, but only four of them were sold. It becomes a very simple query. It connects the two tables by product ID and selects the names and the costs of these products. Remember to subscribe to our channel. We want to select the names and the costs of the products that were not sold.
The subquery returns the product IDs from the sale table the sold products and compares them with the product IDs in the outer query. If a record in the outer query does not find its product ID in the list returned by the subquery, the record is returned.
This query connects the two tables product and sale by the product IDs. The NULL denotes that the product was not sold. The result set contains the products with the sale year other than as well as the products without any records in the sale table. You can rewrite the same query using a JOIN :. One key difference is performance. If execution speed is paramount in your business, then you should favor one over the other. Which one? Read on to find out! I won't leave you in suspense, between Joins and Subqueries, joins tend to execute faster.
In fact, query retrieval time using joins will almost always outperform one that employs a subquery. The result table is referred to as the Cartesian product of the two source tables — the direct product of the two sets. The less fancy name for the same thing is a cross JOIN.
The number of rows in the result table is equal to the number of rows in the first source table multiplied by the number of rows in the second source table. Theoretically, there is no limit to the number of levels of sub-queries that a SQL statement may include, although any given implementation has a practical limit. A key feature of a sub-query is that the table or tables that it references need not be the same as the table or tables referenced by its enclosing query.
Different kinds of nested queries produce different results. Some sub-queries produce a list of values that is then used as input by the enclosing statement. You'll need to see the execution plan. A subquery and a join can have the same query plan. Also, an outer join is not the same as inner join because outer join is extremely slow.
As a matter of fact, it'll be faster to create a temp table with the required data then use the result in an inner join rather than doing an outer join. Your query can also use join, subquery, or subquery in a join. Using subquery in join will be faster than using a subquery in where clause. So I don't believe generalization is a correct answer. You'll need to measure performance and see the execution plan to understand how the RDMS will perform because different engines use different methods.
Products By Bayt. Use Our Mobile App. Get Fresh Updates On your job applications, and stay connected.
0コメント