i'm trying answer question: (general sql syntax)
write query display orders salesman belongs 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 subquery as:
select * orders salesman_id= (select salesman_id salesman city='new york') i know there several answer questions in sql more efficient or reason why use 1 or other? answer below brings same result, i'm wonder if there reason on why use subqueries?
select * orders, salesman orders.salesman_id = salesman.salesman_id , salesman.city='new york' thanks
if subquery returns more 1 row, mysql throw error.
select ... orders o o.salesman_id = ( select s.salesman_id salesman s s.city='new york' ) that is, if there 2 or more rows in salesman have 'new york' in city column, query throw error. workaround that, replace equality comparison in operator.
if write query join operation, don't have problem. , can return column values salesman table.
given suitable indexes, we'd expect query execution plans equivalent, though there cases might not be.
worst case, subquery, execution plan retrieve every row orders, , execute subquery find out if there 1 row in salesman table salesman_id has city value of new york. simple case, wouldn't expect that.
subqueries powerful tool keep in sql toolbelt. , there reasons choose use subquery.
but given result returned particular query, if need return columns orders table, we'd typically write join.
select o.* orders o join salesman s on s.salesman_id = o.salesman_id s.city='new york' (as aside, typically avoid using old-school comma operator join operation, , make use of newer(?) join keyword , join predicates moved on clause.)
