DATABASE SQL JOINS : Quick Learn SQL JOINS with SQL SERVER to Write Complex Queries | Mazhar Hussain | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

DATABASE SQL JOINS : Quick Learn SQL JOINS with SQL SERVER to Write Complex Queries

teacher avatar Mazhar Hussain, Computer Science Lecturer and Programmer

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

8 Lessons (29m)
    • 1. Introduction to JOINS

      1:43
    • 2. Types of JOINS

      1:04
    • 3. Primary & Foreign Key for Relationship

      4:14
    • 4. Tables and Their Relationships

      4:38
    • 5. INNER JOIN

      6:10
    • 6. OUTER JOINS

      5:33
    • 7. CROSS JOIN

      1:52
    • 8. SELF JOIN

      3:32
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

16

Students

--

Projects

About This Class

SQL JOINS are used to combine, retrieve and fetch data from two or more tables, based on a related, common, or matching column between them. In a real-world relational database, data is structured in a large number of tables, and which is why there is a constant need to join these multiple tables based on logical relationships between them.

f3e5b544.png

There are four major types of JOINS that you will learn to write the queries in order to fetch data from multiple tables according to the requirement :

  1. INNER JOIN
  2. OUTER JOINS
  3. CROSS JOIN
  4. SELF JOIN

You will be able to use and write complex and advanced queries using SQL joins to fetch data from multiple tables.

Meet Your Teacher

Teacher Profile Image

Mazhar Hussain

Computer Science Lecturer and Programmer

Teacher

Mazhar Hussain is teaching Computer Science courses since 2015 at the National University of Computer and Emerging Sciences.  He holds a Master's Degree in Computer Science and is passionate to deliver practical knowledge and skills to his students.  He has been teaching DATABASE courses especially SQL SERVER, MYSQL, ORACLE, and  MS ACCESS for more than 5 years span.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction to JOINS: Hello and welcome. This class will give you the concept and use of SQL joins. Joins are used to fetch the data from multiple database tables. In a real world, database data is structured in a large number of tables, and which is why there is a constant need to join these multiple tables based on a logical relationship between them. For example, consider imply management database that contains two tables, implying and job related. And you learn to know the implies name with their job designation. So you are required to retrieve that data from these two tables. In this case, you will use join to fetch data from two tables. As you can see the imply tables, it only contains the employee name and implies designation is in the job table. As you can see, the job name, these two tables has a relationship with that related are common column that is job ID in both tables. So you can use the inner join to retrieve the rows that have matching values based on are related are common column in table, you can see that Curie inner join that will fetch the required data from two tables. And here you can see that zone in the result if we consider the first imply an M joan, and then we'll look into the imply table, job 1, job IDs two. Then if we look into the job id2 in the job table. So job name of job ID 2 is analysed. And in the result you can see John job is analysed. So that's how the giant box at the end of this class, you will be able to do your class project using the giants concepts that you will learn so far. So let's begin to dive into that class. 2. Types of JOINS: You will learn SQL giants to fetch data from multiple database tables. Sql joins are basically used to combine the retrieve and fetch data from two or more tables based on a related column are I've come column are you can say the matching column between them. Basically the relationship between two tables is established using the related column are common column in a real murder relational database data in a large number of table. And that is why there is a constant need to join these multiple tables based on logical relationship between them. We will see in this class how to construct the relationship between two tables based on that related are common column. There are four main types of joins. The first type is inner join, also known as natural joint. The second is outer join, outer join, but that has three divs. Left join, also known as left outer join, right join, also known as the right outer join and full joint, also known as full outer join, 36 cross join and forth is self-adjoint. We will see all of these joints in detail. 3. Primary & Foreign Key for Relationship: Now you will understand the concept of primary and foreign keys who establish the relationship between two tables. So what is primary key? Primary key is basically the unique identification for each record. Primary key is a column in that table whose values uniquely identify a row in our table. For example, consider a student table. So in the student table, the student ID will be unique for each student with other attributes like student name, student address, and telephone numbers to then marks, et cetera. Student ID must be unique. For example, our passport number is our unique identification. So unique identification is basically required to identify the record are the data against each one. So here comes the foreign key. Foreign key is basically is used to establish that relationship between two tables. So foreign key is a column in a table whose values correspond to the values of the primary key in another table, our parent table. So foreign key column will be considered as are related column are common column, ARRA, matching column. Please note primary key is unique, so it can never be duplicated. So foreign key can be duplicate. Now let's understand the concept of why we need the foreign key. Let's consider the department table. Department id, name and location id. In department table, department id is a primary key. So what if we want to store that data fire department implies in the same department table. So what will happen through your department IDs primary key and it can never be duplicated. So against each department, there will be multiple implies. So for example, if we store the data of the implies in the same department table, then we need to replicate their department ID. So we need to store the data file to implies against department number 10, but we cannot replicate the department number then. So that is why we require and other table in which we will use these primary key values to create a foreign key. So here is our employee table. Here we can see the Department ID. So the department id will contain the same values as we have in that primary key. So here you can see in the Department 13 we have replicate values. That'll duplicate values because in that department 30 we have the multiple imply o department ID can be replicated over here. So now in the employee table, the department id will work as a foreign key because we need to store the multiple implies against each department. So let's see the relationship. So here is our department, they will, and then we prize table. So here's you can see the department ICUs primary key in the department, and employee ID is the primary key in the employee table. And here is our foreign key, department id in the employee table that is referring to the department id in the department table. So here it is acting as a primary key, but in the employees table it is acting as a foreign key. So that's how it works and we stablished the relationship between different tables. So now let's move to the SQL Server Management Studio to see that how can we create a primary key and foreign key while creating the tables? And how can we establish that relationship? So first see how to create a department they've been through. And the department table, you can see that department id is declared as a primary key and then other attributes. And in that imply table, you can see the employee ID is a primary key and then other attributes. And finally, you can see the Department ID. We haven't declared the department ID is a foreign key. It is referring to the department id in that department table though here is the department id, but one thing that you need to know, the data type of department id in their department, they will then you can say that our parent table must be same in the child table are in the employee table there you can see the Department ID data type is also in the department. The department id data type is also the empty, just so data types must be same in order to create a foreign key called the department I had in the employee table is referring to the department id in that department table. That's how we construct a relationship between two tables so that we can fetch data from multiple tables. 4. Tables and Their Relationships: Before we begin with the joint, Let's have a look at the tables we will be using. We will be using implies management system. This system contains four Pay Bills, location, departmental, and implying to use the join and go fetch the data from multiple tables, you must understand what is the relationship between these tables. You can see the entity relationship diagram of the implies Management System. Location table has a relationship with department and a department has a relationship with employee table and then imply table has a relationship of each ARP table. For example, relationship between department and imply is one-to-many. This symbol shows one are many. So the relationship is established based on their department id in their department table that is acting the primary key in the department table and foreign key in the employee table. So department id is basically the littered column, are the matching column on which basis that relationship is established between their department and the employee table par exemple. Let's have a look at the department table. You can see the Department ID acting out primary key in the department table and extremely, and in the employee table, department id is acting as a foreign key. And here you can see it can be duplicate. So it means department, there will be multiple implies in the employee table. Similarly, you can see the job ID. And job ID is also an employee table and it is a foreign key. As you can see, the relationship between the employer and the Joe and Joe bank imply has a one-to-many relationship. So it means against a job there will be multiple employees. For example, see the affordance is 6, 7, 1. So we have the three record in the employee table. If we know that 6, 7, one is a manager took three implies our manager and the employee table. Now let's consider that we want to join the department table anti job table. But we know that there is no direct relationship between department and job. If we see the entity relationship diagram, you can see there is no a direct relationship between department and Joe goes, we don't have that littered column or the matching column between these two tables. But Department and jobs are indirectly related to the employee table. So if we want to join their department and the job table first, we will join the department table and the employer table using the department id because department ID is common in BAD department and employee table 2, that is the related, are the matching column on which basis the relationship between department and employee table is established. And we will join the employee table and that job table based on job ID, because the job ID is.com column are the liquid column between employer and the job table so we can join them purple, join their department and the job table, we must go through the employee table because department table is linked to be the job table through the employee table. So implantable is working as a bridge between these two tables and we don't have that direct relationship with the department table and the job table. So that's how that relationship works. So to use the inner and outer join between multiple table, you must first know the relationship between tables. Now we will create these table in the Microsoft SQL Server Management Studio so that we can use them five joints. So firstly, we will create imply management system database, and then we will use this database to create further table into that database. So first we will run the create Curie and then the Execute button, the command is completed successfully. Then we will use this database. You can see here our current data bases master, but we wanted to use the implies management system. So select this Curie and execute. A command is completed successfully. Now you can see here the employees management system is selected. So now we will create tables here. So first table is the location table and then insert data into the location table. And secondly is the job table, and then insert data into the job table. And Thursdays their department table here you can see in that department they built the location id is acting as a foreign key. Then insert data into the department table and then the employee table. Here you can see in the employee table we have two foreign keys, the job ID and the department id, and then insert there and do the employees table. Now I hit the Execute button. So there are tables are created. Now if we refresh our server and then expand the Databases. Now you can see the implies management system and here we can also see their tables. Here you can see the Department imply job and location tables are created. We can also see our table by running that query select static from imply if I hit the execute, but you can see our employee table is created and the data is inserted. Now our database is ready to work with the joint. 5. INNER JOIN: Now you will be able to use the inner join to fetch the data from multiple tables. Let's have a look at the syntax of the inner join to after-death round gloss. Inner join is used between two tables that will be joined or table 1 and they will do will be joined in. A John keyword is used between these two tables that will be joined. Please note t1 is a list for table one and D2 is aliased. They will do. John used the all keyword to specify the condition of either matching columns where T1 refer to the table ones for T1.com column means Table 1.com column is equal to table two, where t is referring to the table to table two.com column. So this will be the matching condition based on the related column are the common column with the values of these two columns will be equal to 0, it will be fetched. John is used to retrieve the rows that have matching value based on unrelated are common column in both tables. So the only rows will be fetched that have the common values in both tables based on that illiterate Column, Null keyword is used to join two tables. I will curie is to display the employee ID, LastName, job ID, we did designation. If we see in their tables, employee ID belong to imply table and last name also belong to the imply table, but job ID belong to the job table. And job designation also belong to the job table. So now we need to fetch the data from two different tables. So we will use the inner join and we know that we will use the related column, the common columns on that basis, we can use the inner join. So let's have a look at the Curie select Employee ID, last name, Joe Biden's dog name from employee E. E is the alias of the employee table so that we can use with the attributes to refer it to two which table it belongs to. Then we'll use the inner join to join two tables, improviser and jaw. And then we mentioned that job table and aliased j is, you can see we have used this aliased with the job. They will add attributes, then we use the keyword. And after Don gave her the who used a matching column are the related columns so that we can represent on that basis. So either job ID belong to the employee table and Jared, our job ID belong to the job table. So here we compare the values of both these columns and we will get there as to where these values matches. For example, if you see the result, let's consider the employee 7369. Employ 7369 in the employee table, its job ID is double 67. And if we match it with the job table here is doubled 6, 7, and double 67, job is color. So here you can see employee ID 7369, last name is Smith job I did double 67 and his job is colored. So in this way the inner join or walks to fade that data with matching values based on unrelated column aura common column between two tables. We will see the example of artists would need to join more than one table is then wet available. Do, let's see the examples. So here is our curator display the employee ID, LastName with their department name and retail groups or employee ID and lastName belong to the employee table. Department name is belonged to their department table, and regional groups belong to the location table. For now we need to join the three tables in order to get the required data and we know the relationship. But first we will join them, imply a table with our department table based on their department id. Then we will join the department table using the location id that is the common column in both tables, location and their department. So here we can see that Curie employee ID, employee lastName, department name, regional group from Location table from and then we will imply a table with their department table. And we will use the inner join based on their department id and employee table add in that department they will add desert of the imply and department John will be further joined with the location table using inner join, using the location ID and department table and using the location id in the location table, Let's see, There is only two. Here is the imply 7369 is last name is Smith, department is 220. Department is a research and its location is one before. And if we compare with the location id 1, 2, 4, and here he's gotten to four in the location table and it is Chicago. And in the result of we can say the department name is a research and regional group is Chicago. Then this way we can retrieve the results from multiple tables. Let's see one more Curie example with the inner join. So here is our Curie. How many implies working in the last two? The last belonged to the location table. So it means we need to join the location table and the employer table because we have to count the number of implies. We're looking in the last. So there is no common column between location and imply table. Or we can say there is no direct relationship between these two table, then we will see the religion of the location payable. So location they will have that relationship with the department table and department they will also have the relationship with the imply table. So first of all, can join the apply table with their department, they bill, and then we can join the department table with their location. So the entity relationship diagram, you can see him kitchen table. So we will use our department table in order to get them there. Now let's run this query in SQL Server Management Studio. So here is our query in which first of all we have agenda implied table and their department table. When we this result we have John, the location table. In the whereClause, we only use the original group, the last, because we want to calculate the number of implies in the last. And also we will use the group by regional group because we have to calculate the number of implies in that last now I will hit Execute button to run this Curie. And here you can see in the last we have or the number of unglamorous. And if we go back to the tables, we can redirect their port number implies belong to that the last. So now let's redirect around that table are many implies belong could the last or location idea of the last is 12, 3. And in their department number here we can see the location id 1, 2, 3. Department id is 13. Now we will see the Department 13, the employee table. So in the employee table you can see vitamin number 13 here, 1, 2, 3, and so its main four number of embolize belong to that the last, so our result is correct. Next movie we'll see the outer join. 6. OUTER JOINS: Let's start with the outer joins. As we know, there are three types of outer giant, left, right, and full outer join. Here is the syntax for the outer joins. We can use the oxygen in between two tables. After the from clause, we will have one and table two. And between these stable we can use a left join, right join our food joint. And then we specify the matching condition after the keyword. We can also use the outer keyword between these joints. For example, we can use the left outer join, right outer join, and full outer join. So you can use the outer joins in both ways. Now you will a left join, left outer join. Left join is used to fetch the rows that have matching values based on unrelated are common column in both tables. And it also include rows from the left table with unmatched values. So that is why it is known as left join because it will give the matching values as well as unmeasurable is from the left table. So here you can see in the figure Alda matching values from there two tables and then unmatched values from the left table, left join keyword is used to join that table to let see the Curie now display all departments names and ID employee ID name because Department 10 does not have any employer and the employee table. So if we use the inner join so department then our accounting department will not be included in our results. So that is why we will use a left join and department table will be on the left side so that it will also face into unmanaged values as well with the mesh to values. Now let's see the Curie lectin department id, department name imply ID, FirstName PRM, department, left, join and then imply and join on department id in both tables. So here you can see department table is on the left side of the joint so that it will fetch all the rows from the left table. Are there department table with unmatched values? So let's see the result. And the result. You can see Department ten from the left table are gram that department table is also in that is irritative shoe notice department then that is accounting in the department table, also included in the results, but it does not have any implying. So that is why employee ID and a firstName is null. Here you can see in the employee table, in their department ID of the imply a table. So remaining rows are the matched row set only first row with department then is the unmatched from the left table are from the department table. Now you will use the right join on right outer join. Right join is used to return the rows that have matching values based on a related are common column in both tables and include all rows from that like table with unmatched values, right? John keyword is used between two tables, or you can also use the right outer join. So here in the figure you can see right join and will fetch rows with the matching values and all the rows from the right table with unmatched values as well. Put a table will be on the right side of the join from which you want to retrieve the unmatched value. So let's see the example curie display imply ID, FirstName, all job ID and job named cert, meaning we want to fetch the unmanaged value from the job table with the master value. So if you see in the job they will joke, I already doubled 69 that these analysts that is not in the employee table job ID, as you can see, there is no imply against the job analyst time the job double 69. But now we will set the job table on the right side of the joint so that it will fetch unmatched values from the job table as well as manage to values. So let's see the Quran. Select Employee ID, FirstName, job ID, job name Brown, implying Dr. join and jaw. As you can see, the job is on the right side of the joint so that it will face unmanaged values, lambda job table as well. It will fit the measured values from both tables imply and jog and the common column between these two tables. Nice job I, because we see the result here in the result you can see there is no imply against our job analysts, the job ID double 69. So there it is the unmatched row in the job table. I'll remaining rows are the rows in both tables except our second row, but that's how that I joined works. Now let's say the full giant, our full outer join, full join is used to retrieve the rows that have the matching values based on a related are common column in both tables. And it includes all the rows from both tables with unmatched two values, full join, our full outer join keyword is used while writing the query. So here in the figure you can see it will include all the matching values and then it will include Alda unmatched values from both tables. Either on the left, are either on the right side. Let's see the Curie display imply a first-name manager ID with our department id and name. So here is our theory. Let firstName manager ID, department id, department name from employee. As you can see, we have used a full outer join. You can also use the full joint and then we have joined the Department ID based on their department ID there it is common Arthur liquid column in both tables. So you can see the result. All the rows are matching rows except the last row. Because against department, our department according there is no employee and the employee table, as you can see, department ID in the employee table. There's no department then in the department id in the employee table. So you can use the full join when you want to repeat the meeting values, as well as unmatched values from both tables. 7. CROSS JOIN: Now you will use the cross join clause. Jan has a different nature as compared to the other joint. Here you can see the cross join syntax. Cross join is used between two tables after the from clause, you can notice there is no matching column condition because cross sign does not work on the principle of unrelated column. Matching column are common cold browser and will relate each row of the first table with each row of the second table. Cross join is basically used to calculate the Cartesian product of the rows from two tables. Let's have a look at this figure. Table one contains two elements, a and b. And table two words, so contains two elements, 12. So in order to calculate the Cartesian products, so each row of table a will be related to the, the, each row of the table B. For example, consider the element a there, he says later to the bot elements of that table two. And similarly the second element is related to the both elements of their table two. So it's, there are two rows in the festival and to-do's in the second table. Then I will multiply the rows of the both tables to calculate the number of rows in the result, Let's see that URI display the Cartesian product of employee ID and lastName with job i, the end job name imply table. We have six rows and in job they blow yeah, 40. So the total number of rows, and our result will be six multiplied by four. So total rows will be 24 in our result after calculating that temptation products. So here is our security. These are the attributes. And then if we have used up cross join between these two tables imply any job. Now let's see, doesn't. So if the results you can see we have the 24 total rows and reinvest that imply ID and last name from employee table and job ID and job name from that job they will do in the zone two, you can see each row in the imply a table we'll relate with each row with the job table. 8. SELF JOIN: Finally, the fourth type of join is self-adjoint cells. John is used to join our table to sell. It works on the principle of related column are matching column to fetch the rows that have the common values, we install the latest column, aura, common column. That's why it's like an inner join. But the difference is that both the matching columns are in that same table, so you need to join this same table. Let's consider the employee ID in the employee table and manager ID in the employee table. So these two columns are the matching Gollum are related column in that same table because each manager is then imply, for example, if we consider the manager 7, 6, 9, 8, you can see 76 1980s and implant. So under each manager there are multiple employers. Now let's see a few reach. So here in the employee table you can see both have the imply name, but the table don't have the manager last name explicitly. Tova need to measure both these columns, ID and manager ID in order to fetch that manager names. For example, if we see 7902 manager ID, so it's Bly IDs 7, 9, 0, 2 and its name is var and work is the manager of the Smith. Now let's have a look at the Curie how we will write that you Rico select LastName as employee name, and there it is. E, employee table m dot last name. Here you can see imply a0 and employ M. So we make the two copies of the same employer table. One copy is E, R, you can say imply, and the other copy of the same table is MR. You can say manager. So the two copies of the same table will be joined on the basis of the matching column manager ID and imply ID. So the manager ID of the e copy with the imply idea of the M copy of that same table. So the last name from the E copy of them blind will be the imply name and the last name from the M copy of the employer table will be considered as a manager name. And that's what we want to retrieve in our curie. So let's see the result. The result that you can see employ a name Smith and he isn't manager is walk. So if we see in the employee table, employee name is summit and his men there is 7 9 0, 2, and 7, 9 0, 2 is Va1. Similarly, if we consider them to buy a doll here and it's mended. Ids 7 8397839 is Baker to here you can see Doyle meant there is Baker. So that's how that Dynavox based on the matching color in that same table. So here you can notice we said that it works like the inner join, but its declaration is different because we have used the matching columns in the where clause because that is one way to write the self-join. We can also use the inner join between two same copies of the appliance. To use the self-adjoint, we will switch to the SQL Server Management Studio in order to see how can we use the energy to write out purifier self-adjoint. So first run the self-adjoint goodie with the where clause. That is one way to write the self join. So let's hit the execute button, and now we will use the inner join to write that purifier, that self-adjoint. So here you can see between their two copies of the same imply table, we have used the inner join and then we specify the column after the keyword. And if I run this query, you can see we get the same results. So there are two ways to write the self-adjoint. In the first method after the forearm, we only specify that two copies of the same implied table. And then we'll use the where clause to mete matching bottom. And the second method is to use the inner join between same copies of the two labels. So you can see that is a bit interesting. So I hope it makes sense to you. I hope you have enjoyed the learning and a good day.