1/10/2024 0 Comments Sqlite inner join on subquery![]() ![]() This is just like the previous situation, but here the subquery is used in a NOT IN operator. Want to learn more about SQL Subqueries with the IN operator? Watch an episode of our We Learn SQL series on Youtube. ![]() This is often necessary if you transform subqueries with an IN or a NOT IN into JOINs. Notice that we also use the DISTINCT keyword to remove duplicate records. It is an INNER JOIN, so if a product doesn’t have its ID in the sale table, it will not be returned. It connects the two tables by product ID and selects the names and the costs of these products. The query below returns the same result by using a JOIN: There are more products in the product table, but only four of them were sold. The subquery selects the product IDs from the sale table ( SELECT product_id FROM sale), so only the sold products are returned by this query in the final result set, like this: name 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. WHERE id IN (SELECT product_id FROM sale) Let’s say we want to obtain the names and the costs of the products sold in our example. In this case, the subquery returns to the outer query a list of values. Subquery Within the IN ClauseĪnother subquery that is easily replaced by a JOIN is the one used in an IN operator. 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. city: the city where the product was sold.year: the year in which the product was sold. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |