Sign in to continue learning
Data I/O and Preprocessing with Python and SQL
24h38m
You'll often find yourself working with multiple tables that contain information about the same entity. You can join them together to find common rows, giving you richer context about that entity. An inner join combines rows from two tables where there are matching values in the columns specified in the on clause. This join only includes the rows present in both tables. The tables can represent the same entity, or they can represent two distinct entities that have a meaningful relationship. For example, let's say you have patient records from two hospitals and want to analyze only the patients admitted to both hospitals. You might write this query. Select star from hospital A, inner join hospital B, on hospital A dot patient ID, equals hospital B dot patient ID. The output will only show the patients that went to both hospital A and hospital B. In this case, you'll need to assume that both hospitals share the same patient ID system, so the same patient has the same ID at each hospital. You can visualize inner joins using a Venn diagram. On the left is the first table. Say it represents hospital A. On the right is the data from hospital B. The inner join represents the overlapping area where the circles intersect. Patients that are unique to hospital A or unique to hospital B are not included. So, inner joins are best for focusing on shared data between two tables. Sometimes data from two tables can't be directly joined because they don't share a common field. In these cases, you can use an intermediate table to act as a bridge. For example, consider a scenario with students, courses, and an intermediate enrollments table. The students table contains information about students, like student ID, name, date of birth, and email address. The courses table contains details about courses, like course ID, name of course, department, and instructor. The enrollments table links students to the courses they are taking with column student ID and course ID. To find all students and the courses they are enrolled in, you could write select students.name, courses.courseName from students. Inner join enrollments on students.studentID equals enrollments.studentID. Inner join courses on enrollments.courseID equals courses.courseID. This query starts by joining the students table with the enrollments table with the student ID as the common field. This new table is then joined with the courses table with the course ID as the common field. In other words, the query uses the enrollments table to connect students to their courses. Using intermediate tables in this way is essential for many-to-many relationships, such as the one with students and courses, since one student can take many courses and one course can have many students. Inner joins are one of the most complex and powerful operations in SQL. There's one more type of join you should be familiar with, the outer join. Follow me to the next video to learn more.