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 
(SELECT ContractId 
 FROM ContractVersion 
 WHERE RateScheduleId = 7)

That works. And my head gets that. But here is a another way to do it using JOINS.


Join the Contract and ContractVersion tables ON their ids

SELECT c.Id FROM Contract AS c 
JOIN ContractVersion AScv 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.

Three way table join

Here is the same idea only applied against three tables (look at these as three ven diagrams between x3 tables. We are trying to get the Name and Role Code for a particular person.

User = ID:UserId = UserRole = Id:RoldId = Role

Where clause way

SELECT Name, Code 
FROM User, Role, UserRole
WHERE Name LIKE '%joel%' 
AND User.Id = UserRole.UserId 
AND Role.Id = UserRole.RoleId 

JOIN way

SELECT User.Name, Role.Code
FROM User 
INNER JOIN UserRole ON SystemUser.Id = UserRole.UserId
INNER JOIN Role ON UserRole.RoleId = Role.Id
WHERE Name LIKE '%joel%'