When to use JOINs
It seems to me that there are two scenarios for using JOIN:
- If the data would have otherwise been duplicated
- If data from one request would otherwise be used in another request
Are these scenarios correct? Are there other scenarios for using JOINs?
EDIT: I think I communicated wrong. I understand how JOIN works, that I'm not so sure about using it , when to use it.
source to share
JOINS are used to share tables along with related information.
Typical situations are where you speak
A user table where a user has specific security settings. The connection will be used in such a way that you can determine what settings the user has.
Users
-UserID
-UserName
UserSecurityRoles
-UserID
-SecurityRoleID
SecurityRoles
-SecurityRoleID
-SecurityRole
SELECT *
FROM Users u INNER JOIN
UserSecurityRoles usr ON u.UserID = usr.UserID INNER JOIN
SecurityRoles sr ON usr.SecurityRoleID = sr.SecurityRoleID
WHERE sr.SecurityRole = 'Admin'
LEFT joins will be used in cases where you want to get all the data from the table on the left, and only the data on the right that matches.
JOINS are used when you start to normalize your table structure. You can create a table with 100 characters in columns where a lot of data can be NULL, or you can normalize tables to avoid too many null columns where you group the relevant data into table structures.
The answer to this Question has a good VERY link that has a graphical display of the use of JOINs
source to share
JOINS are used to return data associated with a relational database. Data can be linked in three ways.
- One to many relationships (a person can have many transactions)
- Many to many relationships (a Doctor may have many patients, but a Patient may have more than one Doctor).
- One to one relationship (one person can only have one passport number)
JOIN DIFFERENT ACTS:
- IN INER JOIN will return data from both tables where the keys in each table match
- LEFT JOIN or RIGHT JOIN will return all rows from one table and data mappings from another table.
- CROSS JOIN will return the product of each table
source to share