Welcome to the SQL Cardinality Tutorial! In this lesson, we'll dive deep into understanding cardinality in SQL, which is a crucial concept for database management. Let's get started!
Cardinality refers to the number of rows that a relationship between two tables or columns can have. In other words, it's the count of instances that can exist between the tables or columns.
Here are the two main types of cardinality:
A one-to-one relationship implies that each row in one table corresponds to exactly one row in another table and vice versa.
Example:
Employees:
- id (Primary Key)
- name
- salary
Employee_Address:
- id (Primary Key)
- employee_id (Foreign Key)
- address
In this example, each employee can have only one address, and each address can be associated with only one employee.
A one-to-many relationship means that one row in a table can be associated with multiple rows in another table, but each row in the other table can only be associated with one row in the first table.
Example:
Departments:
- id (Primary Key)
- department_name
Employees:
- id (Primary Key)
- name
- department_id (Foreign Key)
In this example, each department can have multiple employees, but each employee belongs to only one department.
A many-to-many relationship means that multiple rows in one table can be associated with multiple rows in another table, and vice versa. To establish a many-to-many relationship, we need a joining table.
Example:
Students:
- id (Primary Key)
- name
Courses:
- id (Primary Key)
- course_name
Student_Courses:
- student_id (Foreign Key)
- course_id (Foreign Key)
In this example, each student can enroll in multiple courses, and each course can have multiple students.
What is the difference between a one-to-one (1:1) and one-to-many (1:N) relationship?
Joining tables is essential when working with multiple tables and understanding cardinality. SQL provides various types of joins, such as Inner Join, Left Join, Right Join, and Full Join. We'll cover Inner Join in this tutorial.
Inner Join returns only the matching rows between the two tables being joined.
Example:
Employees:
- id (Primary Key)
- name
- department_id (Foreign Key)
Departments:
- id (Primary Key)
- department_name
SELECT Employees.name, Departments.department_name
FROM Employees
INNER JOIN Departments
ON Employees.department_id = Departments.id;
This query will return the name of employees along with their respective department names.
What does an Inner Join do in SQL?
We hope you found this SQL Cardinality tutorial helpful! Stay tuned for more in-depth lessons on SQL. Happy coding! 💪🚀