CODEWITHSUNDEEP
sqlsql-server
Anoushka Seechurn
Anoushka Seechurn

Reputation: 2266

SQL statement structuring

I have two tables AgentFare and SalesHeader having hdrGuid and hdrGuid , DocumentNumber as columns respectively. I have to perform a query based on Document number, however I am not able to figure out which of the two following queries are most suitable.

SELECT agf.Fare , agf.Tax . agf.Commission 
FROM AgentFare as agf 
  INNER JOIN SalesHeader as h ON agf.hdrGuid = h.hdrGuid AND h.DocumentNumber = 'XYZ'

OR

SELECT agf.Fare , agf.Tax . agf.Commission 
FROM AgentFare as agf 
   INNER JOIN SalesHeader as h ON agf.hdrGuid = h.hdrGuid
WHERE h.DocumentNumber = 'XYZ'

Which of the 2 are more appropriate ?

Upvotes: 1

Views: 59

Answers (3)

Daniel Hollinrake
Daniel Hollinrake

Reputation: 1778

You might find that this is a case of personal preference. My preference is for the second one. It's clear to me what you are joining on and what you are filtering with. If you change your code so that you replace 'XYZ' with a parameter, then for me the second way is much easier. I expect to find the 'filters' at the bottom.

Upvotes: 1

Michaël Benjamin Saerens
Michaël Benjamin Saerens

Reputation: 875

The "ON" statement should be used to define on what columns 2 tables should be joined. The "WHERE" statement is intended to filter your results.

The second option is the more appropriate to use.

Upvotes: 1

K T
K T

Reputation: 199

First method is more appropriate.Since it will take less time as compared to second query.

Upvotes: 2

Related Questions