CODEWITHSUNDEEP
sql
Martin D
Martin D

Reputation: 43

Subquery VS straight query in SQL

I'm trying to answer this question: (General SQL syntax)

Write a query to display all the orders for the salesman who belongs to the city New York

Salesman

salesman_id  name        city        commission
-----------  ----------  ----------  ----------
5001         James Hoog  New York    0.15
5002         Nail Knite  Paris       0.13
5005         Pit Alex    London      0.11
5006         Mc Lyon     Paris       0.14
5003         Lauson Hen  San Jose    0.12
5007         Paul Adam   Rome        0.13

Orders:

ord_no      purch_amt   ord_date    customer_id  salesman_id
----------  ----------  ----------  -----------  -----------
70001       150.5       2012-10-05  3005         5002
70009       270.65      2012-09-10  3001         5005
70002       65.26       2012-10-05  3002         5001
70004       110.5       2012-08-17  3009         5003
70007       948.5       2012-09-10  3005         5002
70005       2400.6      2012-07-27  3007         5001
70008       5760        2012-09-10  3002         5001
70010       1983.43     2012-10-10  3004         5006
70003       2480.4      2012-10-10  3009         5003
70012       250.45      2012-06-27  3008         5002
70011       75.29       2012-08-17  3003         5007
70013       3045.6      2012-04-25  3002         5001

The solution provided is with a SubQuery as:

select * from orders where salesman_id=
(select salesman_id from salesman
where city='New York')

I know there are several answer to questions in SQL but is any that is more efficient or any reason why to use one or the other? The answer below brings the same result, so I'm wonder if there is any reason on why to use subqueries?

select * from orders, salesman 
where orders.salesman_id = salesman.salesman_id and salesman.city='New York'

Thanks

Upvotes: 1

Views: 201

Answers (2)

Matt
Matt

Reputation: 802

You should take a look at you DB's execution plan and you'll be able to see the difference, but the straight query will have the same or better performance depending on the SQL you are using and whether it's smart enough to understand they're equivalent.

Ditto on the syntax comments. It should read:

select * 
from orders
    join salesman
        on orders.salesman_id = salesman.salesman_id
where salesman.city='New York';

Upvotes: 1

spencer7593
spencer7593

Reputation: 108400

If the subquery returns more than one row, then MySQL will throw an error.

SELECT ... 
  FROM orders o
 WHERE o.salesman_id =
       ( SELECT s.salesman_id
           FROM salesman s
          WHERE s.city='New York'
       )

That is, if there are two or more rows in salesman that have 'New York' in the city column, the query would throw an error. To workaround that, we could replace the equality comparison with an IN operator.

If we write the query as JOIN operation, we don't have that problem. And we can also return column values from the salesman table.

Given suitable indexes, we'd expect the query execution plans to be equivalent, though there are some cases where they might not be.

Worst case, with the subquery, the execution plan would be to retrieve every row from orders, and then execute the subquery to find out if there is one row in the salesman table for that salesman_id which has a city value of New York. But with this simple case, we wouldn't expect that.

Subqueries are a powerful tool to keep in the SQL toolbelt. And there are reasons that we will choose to use a subquery.

But given the result returned for this particular query, even if we only need to return columns from the orders table, we'd typically write it as a join.

SELECT o.*
  FROM orders o
  JOIN salesman s
    ON s.salesman_id = o.salesman_id
 WHERE s.city='New York'

(As an aside, we typically avoid using the old-school comma operator for the join operation, and make use of the newer(?) JOIN keyword and the join predicates moved to an ON clause.)

Upvotes: 1

Related Questions