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.
#MYSQL JOIN WITH A SUBQUERY TV#
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.product_id: the identifier of the product sold.city: the city in which the product was made.
This table contains the following columns: We will describe the various scenarios below.Ĭonsider two simple tables, product and sale, which we will use in our examples. Sometimes you have a choice of either, but there are cases in which a subquery is the only real option. Subqueries and JOINs can both be used in a complex query to select data from multiple tables, but they do so in different ways. You can also read more about JOINs in the article “How to Practice SQL JOINs” by Emil Drkušić. It contains over 90 exercises to review and practice different types of JOINs. You can practice SQL JOINs in our interactive SQL JOINs course. It is most frequently used to join tables with primary and foreign keys. It connects two or more tables and selects data from them into a single result set. The JOIN clause does not contain additional queries. Or simply read the article “SQL Subqueries” by Maria Alcaraz. You can learn about subqueries in the Subqueries section in the interactive “SQL Basics” course or practice writing subqueries in the Subqueries section of the “SQL Practice Set” course. Simple subqueries do not rely on the columns in the outer query, whereas correlated subqueries refer to data from the outer query. Usually, there is a main outer query and one or more subqueries nested within the outer query. Subqueries are used in complex SQL queries. There are many situations in which a JOIN is the better solution, and there are others where a subquery is better. One of the challenges in writing SQL queries is choosing whether to use a subquery or a JOIN.
0 kommentar(er)
