Joins in SQL

Summary

Highlights

• Joins enhance SQL functionality by combining multiple table data. 📚
• Inner joins focus on commonality but require a shared column for operation. 🔍
• Outer joins (left and right) wisely fill gaps with nulls to maintain comprehensive data. 🔁
• Full outer joins maximize information retrieval by including all matching/non-matching entries. 🌎
• Natural joins ease operations by automatically detecting join conditions. 🌿

Key Takeaways

• Joins are crucial for combining data from multiple tables in SQL, enabling logical relationships through common columns. 📊
• Inner joins return matching rows from both tables, requiring at least one common column. 🤝
• Left and right outer joins include all rows from one table and matching rows from the other table, using nulls for non-matches. 🔄
• Full outer joins return all rows from both tables, using nulls for non-matching ones. 🌐
• Natural joins automatically establish join conditions based on matching columns and datatypes, without explicit specification. 🧩

Overview

Chapters

• 00:00 - 05:00: Introduction to Joins in SQL The chapter introduces the concept of joins in SQL, building on knowledge from an earlier chapter. The focus is on understanding how join operations can be performed between two tables in SQL.
• 05:00 - 10:00: Basics of Joins and the Importance of Common Columns This chapter introduces the basics of SQL joins, emphasizing their fundamental role in combining data from multiple tables. Joins are crucial when a report requires integrating data distributed across several relations or tables. The chapter sets the stage for understanding how data from various tables can be unified into one, highlighting the necessity of common columns in forming effective joins.
• 10:00 - 15:00: Comparing Joins with Cartesian Product The chapter titled 'Comparing Joins with Cartesian Product' discusses the fundamental operation of joins in SQL. It explains that join operations are used to combine rows from two or more tables based on a specified relationship between the columns of the tables. Joins are considered essential in SQL because they enable the integration of data from different sources into a coherent dataset.
• 15:00 - 25:00: Types of Joins: Inner, Left Outer, Right Outer, Full Outer, and Natural Joins Joins are used to establish logical relationships between tables in a database. The concept is explained using a scenario with two tables: 'employee' and 'department'. The 'employee' table includes attributes such as employee ID and employee name, while the 'department' table includes department ID and department name. The transcript suggests that the next step would be to determine whether a relationship exists between these two tables, hinting at a discussion about the different types of joins.
• 25:00 - 40:00: Inner Join Syntax and Example This chapter discusses the concept of an inner join in databases, focusing on the need for a common column between tables to establish a logical relationship. If tables do not share a common column, like an employee table with employee IDs and names and a department table with department IDs and names, no logical relationship or inner join can be established. The chapter underscores the importance of having a matching column for creating a meaningful join between tables, which is essential for database queries and data integration.
• 40:00 - 60:00: Left Outer Join Syntax and Example The chapter highlights the importance of having a common column between two tables for performing a join operation. Without a common column, establishing a logical relationship between tables is impossible, thereby making the join operation unfeasible. Hence, a common column is essential for executing any join operation.
• 60:00 - 75:00: Right Outer Join Syntax and Example This chapter discusses the scenario where there are no common columns between two or more tables in a database. It raises the question of how to join such tables and then provides the solution by introducing the concept of a Cartesian product. The Cartesian product operation is detailed, which involves combining all rows from one table with all rows from another, fundamentally allowing a 'join' to occur without common columns. This serves as a foundational concept progressing towards understanding more complex join operations like the Right Outer Join.
• 75:00 - 90:00: Full Outer Join Syntax and Example The chapter explains the concept of a Full Outer Join in the context of database management. It begins with describing the Cartesian product of two tables, which combines every row from one table with every row from another, resulting in a number of rows equal to the product of the number of rows in each table. This is contrasted with a Full Outer Join, which is not fully described in the provided transcript but typically includes all records when there is a match in either left or right table records.
• 90:00 - 100:00: Natural Join Explanation This chapter provides an introduction to the concept of joins in SQL, focusing specifically on the natural join. It emphasizes the necessity of having at least one common column to perform a join operation, as it helps establish a logical relationship between the tables involved. The chapter aims to clarify the basic understanding of joins before delving into different types of joins and how these operations can be executed using SQL.
• 100:00 - 105:00: Conclusion and Next Steps The chapter titled 'Conclusion and Next Steps' provides an overview of different types of database joins, specifically focusing on inner joins, left outer joins, right outer joins, full outer joins, and natural joins. It highlights the differences in terminology between database systems such as Oracle and MySQL, emphasizing generic names like 'left join' used in MySQL compared to 'left outer join' in Oracle. This chapter aims to clarify the nuances in database join techniques as a part of concluding the subject studied in previous chapters.]}

Joins in SQL Transcription

• 00:00 - 00:30 [Music] hello everyone welcome back in this presentation we are going to understand about joins in SQL before stepping into the topic of the day we need to understand what are join operations we have already seen about this in chapter 2 of this playlist in this presentation we are going to focus on how join operations can be performed between two tables in SQL before stepping into the
• 00:30 - 01:00 syntax and examples let's quickly understand the basics of join it is obviously one of the fundamental operations in SQL because we may be having a situation where the data is distributed among multiple relations and we need a report by taking the data from two or more relations or tables in that case we need join operations simply speaking the data may be residing in multiple tables in order to get the data from multiple tables in a single table
• 01:00 - 01:30 then we can go for join operations and that is why this is considered to be one of the fundamental operations in SQL so what it is going to do it's basically going to combine the rows present in two relations so obviously it is used to combine rows from two or more relations or tables what kind of relationship this join is actually performing when we say join is going to combine the result from two or more tables obviously it is going
• 01:30 - 02:00 to establish some logical relationship isn't it that is why join is considered to establish a logical relationship between tables how this logical relationship is going to be established let me give you a scenario let's contemplate this we have two tables one is employee another one is department let us assume employee table contains only two attributes like employee ID and employee name department table contains department ID and department name do we have any relationship between these two
• 02:00 - 02:30 definitely no because employee table contains only employee ID and employee name department table contains department ID and department name how logical relationship can be established between these two tables we cannot perform logical relationship between these two tables because there are no matching column or common column between these two tables so when we have a common column or attribute between two tables obviously we will be able to establish a logical relationship between
• 02:30 - 03:00 these two tables kindly make a note of this when we want to perform a join operation there should be a common column between these two tables otherwise how we will be able to establish a logical relationship no common columns between tables join cannot be performed so it's clear that we need a common column in order to join the tables what if there are no common columns in that case will we be able to perform join operation definitely we will not be able to perform a join
• 03:00 - 03:30 operation when there are no common columns between two or more relations in that case still I want to join two tables what should I do pause this video for a while and think about it i hope you all are done in that case we need to go for cartesian product can you recolct in relational algebra we have seen one of the operations the cartesian product what it is going to do it is just going to combine all rows from one table with all rows on the
• 03:30 - 04:00 other table say for example if one table contains five rows and another table contains six rows so 5 multiplied by 6 30 rows will be there in the output when we perform cartesian product between these two tables now we need to understand how these joints are different from cartesian product if we want the cartian product output there is no restriction like we should have a common column a cartian product between two tables meaning all the rows in one table is going to be joined with all the
• 04:00 - 04:30 rows in the other table whereas in join we need at least a common column at least one common column should be there in order to perform join otherwise we will not be able to establish a logical relationship between the tables i hope the basics about the joins are clear now let's see how these joins can be performed in SQL let's see the various types of joins here we go the first type of join that we are going to see is the
• 04:30 - 05:00 inner join the second type is going to be the left outer join the third type is going to be the right outer join the fourth type is going to be the full outer join and the last one we are going to address is the natural join please be noted that these are the names in generic I have given here say for example Oracle uses the term inner join left outer join right outer join full outer join etc whereas MySQL will refer left outer join as left join and right
• 05:00 - 05:30 outer join as right join also additional join operations may be present depending on the vendors so before proceeding with the syntax of join operations we need to check the documentation of that particular software so that we will be able to apply the exact syntax according to the vendor don't worry about this now whatever we have seen theoretically in this chapter we are going to execute all these things in the next chapter which is the MySQL practicals that time I will
• 05:30 - 06:00 be able to show you how these join operations can be performed in MySQL for now we will focus on in generic aspects joins in SQL at first we will start with the inner join what do we mean by inner join we have already seen about the inner join elaborately now let's see how this inner join can be done in SQL with the help of syntax and example in the previous chapter we understood about the basics now we are going to see some examples in SQL let's see the syntax now
• 06:00 - 06:30 here we go how to do join to be precise in a join in SQL obviously we are going to select the output from two or more relations right so in all the examples whatever I am showing I'm going to use only two tables how to select we know selection can be applied on multiple columns right we can retrieve all the columns or specified columns let's say if we are involving two tables say for example table one and table two and we
• 06:30 - 07:00 going to perform what operation the inner join right so I going to perform table one inner join table two obviously multiple columns are involved this may have n number of columns this may have m number of columns so we want to retrieve only specified columns the column name may be the same in both the tables because join requires at least one common column what if the column name is the same in both the tables in that case we need to resolve this conflict isn't it and that is why instead of simply
• 07:00 - 07:30 retrieving column 1 column 2 something like this i going to specify the table name before the column name with the help of a dot operator meaning table 1's the specified column table 2's the specified column you can use any number of columns as per the availability in table one and table two now let's go to the syntax select the required columns table 1 comma column 1 comma table 1 comma column 2 we can extend it as per
• 07:30 - 08:00 our requirement here I'm taking table 2 column 2 how many number of columns we want we can add all those things then from table one inner join table two so here we are going to perform table one inner join table two and these columns are going to be retrieved from the output of this operation which is table one inner join table two so the output of table one inner join table two is going to be a resultant table from that output table we are going to retrieve
• 08:00 - 08:30 the required columns now what is inner join inner join give the result that are matching in both the tables isn't it so matching rows from both the tables will be the output of inner join so inner join requires a condition and that condition should be provided after on what is the condition table one's matching column should be equal to table 2's matching column remember in order to perform an inner joining we need at least one matching column between two
• 08:30 - 09:00 tables we may have many matching columns however we need at least one matching column and we can choose any matching column as per our requirement here the join condition is specified with the comparison operator is equal to we can use any comparison operators like greater than greater than or equal to lesser than lesser than or equal to or not equal to depending on our requirement so this is the syntax of inner join when I show some examples then it will be easy for you to
• 09:00 - 09:30 understand let's see an example in this slide now in the next slide I will bring two tables i will apply inner join and I will show you the output of inner join now what we are required to focus on is the example here we go just see I'm selecting from employee tables employee ID department tables department name columns can you see here this is employee table this is department table from employee table I'm retrieving employee ID from department table I'm
• 09:30 - 10:00 retrieving department name from employee inner join department so when we join these two tables we get the output table from the output table we are retrieving these two columns because in the output table we may have the same number of columns appearing twice one may be from this table another may be from this table if the column name is the same if we simply mention the column name our system doesn't know which column name we are referring to whether we are referring to the output from this table or from this table and we resolve this
• 10:00 - 10:30 ambiguity by giving the fully qualified name like this table name dot employee ID column name let's see the condition for this inner join employee department ID is equal to department ID can you see here there is a common column department id that is present on both the tables employee table as well as department table let's say if we are simply retrieving department ID I mean department ID the output of this join
• 10:30 - 11:00 will contain two columns department ID and department ID one from employee another from department so this select doesn't know which column I am referring to whether the column from this or this but this approach will help us resolve this ambiguity now let's understand this example with two tables I'll bring this example to a new slide here we go you see the same example select employee employee ID E comma department do department name from employee inner join
• 11:00 - 11:30 department so here is the join operation and the join condition is on employee tables employee ID should be equal to department tables department ID let me bring in both the tables what are the tables employee and department here we go here is the employee table here is the department table just see employee table contains three columns employee ID department ID employee name department table contains department ID department name and department zone we need to
• 11:30 - 12:00 understand whether joins can be performed on this or not we can see join can be performed on this because there is a common column department ID and department ID here as well so obviously join can be performed remember the name need not match in order to perform the join this may have a different name this may have a different name no problem in that only thing is the data type of this should match if the data type is matching then we can perform the join operation and that's why here we are giving the join condition as employee
• 12:00 - 12:30 tables department ID should be equal to department tables department ID so we are comparing this department ID with this department ID now let's see how inner join works inner join actually works on the matching rows from both the tables right let's see the matching rows two is here two is here three is here three is here so the output is going to contain six columns this part please focus on this part this is going to contain three columns here three columns
• 12:30 - 13:00 here so obviously six columns will be there from this output from these six columns we are going to retrieve only two columns employee ID from employee table which is this and department name from this because this table does not contain department name now we can see two rows are going to be there in the output they are 101 which is this 101 is actually Sujit Singh who is working for the department ID 2 what's the department name department name is
• 13:00 - 13:30 finance so we are getting the output 101 finance as the first row and we also get 103 why because matching rows from both the table we have two and three are matching in both the tables and that's why inner join gives matching rows from both the tables so we get 103 Risha upput while retrieving we are not taking the name we are getting the department name which is sales because three is belonging to sales so 103 sales so the
• 13:30 - 14:00 output of inner join is going to be only two rows why because it's going to retrieve the data based on matching rows from both the tables i hope the explanation is clear to you in case we want name I mean employee name and department zone as well you can add those columns here by simply giving a comma employee do employee name department.E department zone this is as per our requirement we can extend the number of columns i hope inner join is
• 14:00 - 14:30 clear to you as we are now clear with this inner join I will start explaining the other joins easily the reason is we are going to use the same two tables employee as well as department let's now move on to the second type of join that we are going to see today which is the left outer join let's see the syntax the same syntax only difference is instead of inner join we are going to use the keyword left outer join see here the syntax selecting table one column 1
• 14:30 - 15:00 table two column 2 as per our requirement from table one left outer join table two and the join condition is on table 1's matching column is equal to table 2's matching column remember as I already mentioned any join that we want to perform we need a common column that is available in both the relations or tables let's see the example selecting employee tables employee ID department tables department name from employee left out of join department on the join
• 15:00 - 15:30 condition employees department ID should be equal to department's department ID let me bring in the same example to the next slide like this the same example you can see it here but this time it's left out a join now let me bring in both the tables the employee table and the department table in a join just combined matching rows from two tables and that's why we get two and three because two and alone are matching in both the tables what about left outer join left outer
• 15:30 - 16:00 join means what is the left side of this operation left outer join what is the left side employee so all the rows from employee table will be there in the output and matching rows from the right hand side table remember what operation it is left outer join meaning all the rows from left hand side and only matching rows from the right hand side meaning in the output we will be getting the employee ID because that is what we are retrieving we will get 101 102 and
• 16:00 - 16:30 103 but only matching rows from the right hand side matching rows are 2 and 3 right 2 and 3 however for four there is no matching row what value will be there in the output it will be a null value i already told you about this in the previous chapter so the output is going to contain all the rows from the left hand side table which is employee table and that is why it is mentioned here because left hand side is employee table so we get all the employee ids 101
• 16:30 - 17:00 102 and 103 from left hand side table and only matching rows from the right hand side table matching rows are 2 and 3 right for two it is finance three it is sales can you see here two 101 is two so 101 is finance 103 is sales because Risha up who is having the employee ID 103 is belonging to the department 3 means sales what about four actually the employee ID pertaining to this department ID four is 102 102 will be
• 17:00 - 17:30 there because it's a left outer join what about the value four which is not there here in the right hand side table for which it uses a null value so this is about the left outer joint with this knowledge let's move on to the right outer join in the left outer join all the rows from the left hand side table will be appearing in the output in right outer join all the rows from the right hand side table will be there in the output that's the difference between left outer join and right outer join so
• 17:30 - 18:00 the syntax is going to be the same with the only difference instead of left outer join it will be right outer join and the example is also going to be the same with only difference is instead of the previous example which had left out join here it is going to use right outer join that's it now let's bring in the same example here here we go the same example operation is right outer join let's bring in the same two tables employee and department now we know output is going to contain all the
• 18:00 - 18:30 department names because department table is the right hand side table so we get all the department names like operations finance and sales and only matching rows from the left table what are the matching rows two and three right so employee ids pertaining to two and three will be there in the output table and in this table you can see there is no one working for operations but operations also will be there in the output because we are retrieving all the values from right hand side table the
• 18:30 - 19:00 department name will contain all the values in the output right now if you match the left hand side we can see for finance it's 101 because Sujit is working for department ID 2 and that's why 101 means finance is there 103 sales is also there because sales 3 means it's 103 can you see here but what about operations no one in this table is working for operations so that is why we can see the employee ID as null in the
• 19:00 - 19:30 join operation if there are no values It uses a special type of value what's that null value null means it's neither zero nor blank it could be unknown or missing or not available i hope right outer join is also clear to you now let's step into the next category the full outer join in the left outer join we got all the rows from the left hand side table and matching rows from the right hand side table in right outer join we got all the rows from the right hand side table and
• 19:30 - 20:00 matching rows from the left hand side table if we combine both it's full outer join meaning both matching and non-matching rows from both the table will be there in the output the syntax is going to be the same so I'm not going to spend more time on this because the only difference here is instead of left or right or inner we are going to use the full outer join keyword so full outer join here also full outer join between these two tables let me bring in the same example to the new slide here
• 20:00 - 20:30 here we go let me bring in both the tables employee and department now let's analyze how the output looks like so our output will definitely contain employee ID all three employee ids also department name all three department names only thing is wherever it is not matching it uses a null value can you see here in the output we get 101 102 and 103 from left hand side all the values similarly from the right hand
• 20:30 - 21:00 side also all the values operations sales and finance but if you see 102 is working for department ID 4 that doesn't exist here so it is a null here similarly operations I mean the department ID 1 there is no one working for department ID 1 and that's why we get null here for operations i hope full outer join is also clear to you now let's move on to the last type of join which is the natural join natural join
• 21:00 - 21:30 is equivalent to inner join in inner join we explicitly mention the condition right if you see just recolct the inner joint topic we explicitly mention the condition i mean the join condition table one's which column should be compared with table two's which column this we explicitly mentioned in the inner join if we don't explicitly mention that we give the task to the database software to find the matching column and do the join operation then it is called as natural join to be precise
• 21:30 - 22:00 inner join we mention the condition I mean the join condition explicitly using the keyword on whereas in natural join we won't specify the condition explicitly and system will implicitly take a join condition and perform the operation remember in both the tables there should be at least one column having the similar data type and the syntax is selecting the required columns from table one natural join table two can you see here there is no on keyword
• 22:00 - 22:30 and condition because we ask our system to implicitly do this join without explicitly mentioning the join condition obviously the example looks like this selecting the required columns from employee table natural join department table let me bring in the same example here see here selecting the required columns from employee natural join department obviously we'll have the same two tables employee table and the department table now you see I didn't explicitly mention the condition the
• 22:30 - 23:00 matching data type here will be the department ID it's not just the name is matching even the data type is matching here right we don't know in employee name it could be var 50 whereas department name could be var 40 now you may be thinking what could be the comparison operator used for this joint condition because we have comparison operators like is equal to not equal to greater than or greater than equal to lesser than or lesser than equal to
• 23:00 - 23:30 which one it will use by default it will use an equin conditional operator which is is equal to so the comparison will be like this employee table I mean employee do department ID is equal to department tables department ID remember natural join will not use operators like greater than or lesser than or something like this it will be an equin so the output is going to contain the same output like the inner join because basically it is
• 23:30 - 24:00 an inner join with department ids comparison and that's it guys i hope the various types of join operations are clear to you don't worry about the demonstration of these two tables we are going to see that in the next chapter using MySQL software with the same two tables for performing various join operations in MySQL i hope the session is informative i'll see you in the next presentation and thank you for watching [Applause]
• 24:00 - 24:30 [Music]