My brain doesn’t process SQL that well. Here are some notes to help me remember.

Say you have the following table structure:
* Contract – Id
* ContractVersion – ContractId, RateScheduleId
* RateSchedule – Id

And you want all the contracts that use a given RateScheduleid. Here are two ways of to do it.

Non JOIN inline SQL way

Grab all the contractIds that use this RateSchedule from the ContractVersion table

select ContractId from ContractVersion where RateScheduleId = 7

And then inline those into the select on the Contract table using the IN command

select * from Contract where Id IN (select ContractId from ContractVersion where RateScheduleId = 7)

That works. And my head gets that. But here is a better way to do it. A more SQL way using a JOIN.

JOIN ON

Join the Contract and ContractVersion tables ON their ids

select c.Id from Contract as c JOIN ContractVersion as cv ON c.Id = cv.ContractId

and then drop in the same where clause

where RateScheduleId = 7

So the whole thing looks like this

select c.Id from Contract as c JOIN ContractVersion as cv ON c.Id = cv.ContractId WHERE RateScheduleId = 7

This uses a JOIN instead of inline the SQL from before. It’s better as it more closely aligns with how SQL should work. But I still find SQL and set theory some getting used to.

Happy coding!

Advertisements