Data Relationships Flashcards
Types of Data Relationships
1: 1 one to one
1: M one to many
M:M many to many
1:1 one to one
one:one (1:1)
Single table rarely two tables PK & FK
relationships i.e
1 employee belongs to 1 organization
1:M one : many
1:M one to many
Two tables with PK & FK relationship
Parent/Children - Single order many items
Example: 1 person has many cars
1 company has many employees
M:M many to many
M:M many to many
Junction table keys from Both tables forming PK
Tricky to Represent
Examples: Student / Subject
1 Student has many Subjects
1 Subject has many Students
Employee/ Department
SQL Joins
Combines 2+ tables, based on a common field
Types of Joins
- Inner Join
- Left Join
- Right Join
- Outer Join
- Full Join
Inner Join
Inner Join - All rows where at least 1 match in both tables.
From A Inner B A = B key. xx = shared data
x x
A x x B
x x
Left Join
Left Join - All rows from left table, match rows from right
Select From A Left join B on A key = B key where B key = null
Display A data that is not shared with B === data not shared
AABB
AAA====BBB
Select From A Left join B on A Key = B Key
Display all A data including data shared with B === data shared
AABB
AAA====BBB
Right Join
Right Join - All rows from Right table match from left
Select * From A Right Join B on A = B (retrieves all B data and shared A data)
Select * From A Right Join B on A = B Where A = NULL (retrieves B data that is not shared with A data)
Outer Join
Outer Join - Select From Table A Full Outer Join B
ON A.Key = B. Key
Where A.Key or B.Key = Null
The data shared between A and B is NOT selected
Full Join
Full Join - Select From A Full Outer Join B
Key A = Key B
Retrieves all data from both tables
UNION / UNION ALL
Intersection
2 tables list match records
