Subquery VS straight query in SQL

Question

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

I could be wrong, but I think the method of joining tables in your second example was deprecated a long time ago. Explicit joins are used much more commonly these days, if for no other reason than improved readability. — CactusCake, Jun 06 '16 at 20:50
Please don't develop the habit of using the old (pre-1992) syntax. Not only is it deprecated, but it is an ambiguous grammar, and both LEFT and RIGHT outer joins are incomplete because of the grammar ambiguities resolved through making the operator left-associative. The semantics also varies between vendors, so your outer joins may reverse on occasion, or be misunderstoof by a reader. — Pieter Geerkens, Jun 06 '16 at 20:50
Note that your first query will most likely fail because the sub-query will return more then one row so you would need to use `IN` not `=`. But a join is not a replacement for an `IN` query. Those two statements do not necessarily return the same thing (I am not going to comment on the outdated, ancient and fragile implicit join in the where clause. Pieter already said enough) — , Jun 06 '16 at 20:56
@PieterGeerkens so you are saying that the straight query is deprecated? — Martin D, Jun 06 '16 at 20:59
He's saying that if you do write a "straight" query, you should do it using ANSI style joins. It's the style of join you used that is considered deprecated. — sstan, Jun 06 '16 at 21:00
@PieterGeerkens where can I find an up to date syntax manual? — Martin D, Jun 06 '16 at 21:00
Possible duplicate of [Join vs. sub-query](http://stackoverflow.com/questions/2577174/join-vs-sub-query) — Matt, Jun 06 '16 at 21:04
*where can I find an up to date syntax manual?* msdn.microsoft.com (assuming t-sql) is probably the "official" source, but I sometimes find their way of explaining things a little, err, chewy. Here is a friendly explanation with links to additional sources: http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins — CactusCake, Jun 06 '16 at 21:12

Matt · Answer 1 · 2016-06-06T21:04:06.690

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';

score 1 · Answer 2 · answered Jun 06 '16 at 21:03

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.)

Subquery VS straight query in SQL

2 Answers2