When to use JOINs

Question

It seems to me that there are two scenarios in which to use JOINs:

1. When data would otherwise be duplicated
2. When data from one query would otherwise be used in another query

Are these scenarios right? Are there any other scenarios in which to use JOIN?

EDIT: I think I've miscommunicated. I understand how a JOIN works, what I'm not so sure about is when to use one.

Answer 1

JOINS are used to JOIN tables together with related information.

Tipical situations are where you have lets say

A user table where the user has specific security settings. The join would be used such that you can determine which 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 the cases where you wish to retrieve all the data from the table in the left hand side, and only data from the right that match.

JOINS are used when you start normalizing your table structure. You can crete a table with 100s on columns, where a lot of the data could possibly be NULL, or you can normalize the tables, such that you avoid having too many columns with null values, where you group the appropriate data into table structures.

The answer to this Question has a VERY good link that has graphical display of using JOINs

Answer 2

JOINS are used to return data that is related in a relational database. Data can be related in 3 ways

• One to Many relationship (A Person can have many Transactions)
• Many to Many relationship (A Doctor can have many Patients, but a Patient can have more than one Doctor)
• One to One relationship (One Person can have exactly one Passport number)

JOINS come in various flavours:

• AN INNER JOIN will return data from both tables where the keys in each table match
• A LEFT JOIN or RIGHT JOIN will return all the rows from one table and matching data from the other table
• A CROSS JOIN will return the product of each table

Answer 3

You use joins when you need information from more than one table :)