At the end, the rows are filtered by a WHERE clause to select the record when the sale price of the product equals $2,000. 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 query, we connect the two tables product and sale with a JOIN operator. We can build a JOIN structure and obtain the same result. Only two products were sold at $2,000: the armchair and the TV table. This is a correlated subquery, since the second condition in the subquery references a column in the outer query. It then uses the product IDs ( product_id) in the selected sales to identify the records from the product table ( product_id=product.id). The subquery first filters the records to only those with the sale price equal to $2,000 ( price=2000). The sale table contains sales records of the products. Since we don’t want all of the products, we use a WHERE clause to filter the rows to the product IDs returned by the subquery. The outer query selects the names ( name) and the cost ( cost) of the products. Suppose we need the names and the costs of the products that were sold for $2,000. A scalar subquery returns a single value (one column and one row) to be used by the outer query. The first such case is the scalar subquery. So, we will focus first on when you can replace a subquery with a JOIN for better efficiency and readability. JOINs are also easier to read as the queries become more complex. While subqueries may be easier to understand and use for many SQL users, JOINs are often more efficient. SQL beginners often use subqueries when the same results can be achieved with JOINs. We will use these two tables to write complex queries with subqueries and JOINs.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |