1

I am in the process of teaching myself SQL queries. My question arrives from w3resource.com sql exercise 6 in the sub-query set of questions. To summarize the question, we want a query that displays the commission of the salesmen serving customers in Paris. Here are the two tables with their columns given:

Salesman(salesman_id, name, city, commission)

Customer(customer_id, cust_name, city, grade, salesman_id)

Below are two queries that I wrote that achieve the solution in different ways. My question is if there is a more 'correct' one out of the two? With 'correct' taking into account performance, standards, etc. Or are both equally fine? I ask because I imagine as I continue with more complex queries, should I be sticking to one for performance/versatility reasons? Thanks

select commission from Salesman
where salesman_id IN
(select salesman_id from Customer
where city = 'Paris')

select commission from Salesman
join Customer on (Salesman.salesman_id = Customer.salesman_id)
where Customer.city = 'Paris'
jbl
  • 11
  • 2
  • https://stackoverflow.com/questions/1200295/sql-join-vs-in-performance – Shoaeb May 16 '20 at 22:25
  • also you should try explain plan for both the queries so you will know how mysql interprets them.. – Shoaeb May 16 '20 at 22:25
  • The queries produce different results. In case there are multiple Paris-customers with same salesman, you will get that salesman's comission multiple times in the second query. – slaakso May 16 '20 at 22:28

1 Answers1

2

They're both fine.

In terms of style, both have their advantages, e.g.

  • Using the IN clause simplifies the main SQL query (from a readability point of view) since you don't have to do a join. If you have several other joins it's nice to reduce them as much as possible to keep track of what's going on.
  • Using the JOIN is needed if you had a composite key instead of a single salesman_id.

They might generate different query plans depending on your database, but like with most code it's better to start by writing for readability & maintainability and optimise only the bits you need to. There's not going to be any difference for this sort of thing.

Rory
  • 40,559
  • 52
  • 175
  • 261