Difference between Equi Join and Non Equi Join in SQL Server | MSSQL Training | By Mr.Sudhakar L

[Music] hi guys welcome to Nativity this is sooo darker and today we are going to discussing what about joints and in this joints equi-join and non-equal how it will work and what is the differences in between Equis ad non-equal okay so that we will discuss here and in this session now and before that so first of all joints what exactly we know that okay so now we are going to discussing your joints today okay so in this in the last video session in my previous video session I was given clear information about joints or joints types and categories everything I was given there so in this session we are going to check the the implementation of your equi and non-equal joints okay so basically joints we know that I think everyone knows that the joints we are going to using for rip driving retrieving data from multiple tables multiple tables tables at a time so retrieving data from multiple tables at a time this is what the joint the nature and requirement so in this the basically two joints okay we discuss in this session and the remaining joints so we have already in the previous sessions here so now what I am going to discussing here first or first of all I'm going to take your equi-join and next we are going to discussing one of another joint is non EP join okay so equi non-equal what is the difference first we will discuss the differences in between equi and non-equal joints okay so what is the differences here a little bit okay absorb so equi-join whatever it was there equi-join okay so equation we are called as a whenever the user is retrieving whenever the user is retrieving data from thee from the multiple tables from the multiple tables from the multiple tables based on based on and equality condition equality condition so equality condition it means what now it means that I'm going to using only one operator that operator is equal operator I'm using what not equal operator so whenever the user is retiring the data based on equality condition then it is called as the equation simply we can say okay but in this equation but in this equation I can't use I can't use okay I can't use the following operators okay I can't use the following operators I can't use the following operators are what kind of operators I can't use means here I can't use my following operators are like for example less than greater than less than or equal to greater than or equals to okay comma not less than gamma not greater than gamma not is equals to so these operators I can't use in my equation okay so except equal condition the remaining greater than conditions or less than conditions greater than R equals two conditions any kind of conditions we can't use except equality condition so that means based on equal to condition only I'm going to retrieving the data from multiple tables this is what the equation definition we called it where as come to non-equal join non equation or how it will be work means so it's a quite opposite of your equation it's so quite opposite of your equation okay what is that means so this was going to retrieving data this was going to retry on the data from the same multiple tables some same multiple tables based on based on any condition based on any condition based on any condition except except equal condition except what now equal condition so that means what is here except equal condition you can use any condition to retrieve your information from the multiple tables but don't use a equal operator okay so that is what when you go for like this then it is called a non equal join we called and non equal join okay what it was say I cannot use this I cannot use I cannot use I cannot use equal operator I cannot use equal operator okay but I can use what I can use I can use the following operators like these all operators I can use I can use these are all operators these operators and even the along with this you can the even though we can use between comma and okay so etc we can use but don't use equal operator or accept equal operator you can use any other operator to retrieving your data from multiple tables so this is what simply we can say equal join and non equally join okay let us see I will show you one small example equi and non equal joints how it will be work okay so come to open your SQL Server management studio and I am going to connecting here and now I am taking some new query editor for writing my queries yes later same I just take my database that is called am my class database I just call him now okay I just go to execute or not so first of all okay I'm going to check your I'm going to check here so I'm going to take him now first of all I'm going to take an example example on equals what I'm showing now so example oh equally dry okay this is my equal okay I'm use you know so but if I want to go for equal join simply I'm going to creating a small tables okay some sample tables I'm creating here table 1 T 1 off okay otherwise I want to take here for example the previously I was tester tables are there so what are the tables are they just I just removed those tables okay and now I'm going to take here test one off suppose I'm giving serial number in teaser and some names I'm passing here back here of some size okay and now this is one table I created not yes it was created in the same patter on one more table I'm creating that I can say the test number two okay and again I'm going to execute on this one okay so this is also executed so that means two tables I created now and what I won't do now whatever the tables I created in my database okay T 1 test 1 and test 2 now I'm good in setting some data but the water over the data I mean setting here that data I mean setting true graphically so whenever you want to insert graphically you just a select to a table in which table do you want in set your data select your table and come to click right mouse button and after that you have an option will be there edit top 200 rows okay so on this i just come click on edit top 200 rows okay so now it will be open your table and later I mean sitting some data into this for example serial number 10 some name I was given now for example psy and 20 I was given some name is atoms like 30 I'm given like James so this is what now 1 and some records I insert a deal and just executed and later in the same pattern test number two also I'm going to take them now so 82 this one again I just come back here to take serial numbers again so 10 I'm going to take here some name is for example what not and I will take your 20 okay so some name I'm going to take in here for example we can say L n okay so this is what I'm going to take in here and one more record I'm taking your 14 the 40 number I will take tower for example Jones okay so these are some mori course I'm going to storing in our second table that's over and the two tables I prepared in my database finally these two tables I just calling now select a star from test one is one table select the star from test-tube is my second table so now I'm going to take this mutant now then you can see that I'm going to exclude this to table the two table data was there in one table I was taken 10 20 30 and some names second table I was taken 10 20 40 some names now on this two tables data I want to apply my equation so equation how to apply here observe I'm going to take in some examples on these two tables and the previous sessions if you want to see my previous sessions there I was given what is the syntax to join more than two or more than two tables thus index I was given now so if you see the my previous sessions and previous videos then I was given clear syntax for joins okay so now why is what is my joint syntax is select a star from first table that I can say test one okay comma next one is what now test two okay and later I will take your where the condition joining condition I want to be what is join condition your test one dot common column the serial number is my common column is equals to tester - okay dot serial number then what is happening here this is called the Equality condition that means whatever the condition I was given in this this is called a equality condition and I'm using equal condition only so when I'm using equal condition then it is called as they equal Joanne we called then is called as what now equation so whenever I am going to execute at this join and two T and X you turn now yes so what is happening here the two table informations serial number and serial number name whatever it was there but two names I Adams is my test one table people name and whatnot Ln visa tested two table people here but whatever the information would call what did he hear this information we got it only matching the data we got it only matching data okay so 10 10 20 getting matching data but in my table I have a nun matching rate also there now if you go for test one if you see the test one unmatching later 30 is there if you go to test two unmatching later forties there but in my example quarry when I done example quarry what is happen only matching the eight hours driving so only Madan matching data is the driving means it means what now we are using we are using what now equals P condition so based on equality condition equal to means matching data now it's matching here test one serial number 10 and tester two serial number 10 both are satisfy the condition so 10 10 satisfied 28 only satisfied so that means whatever the Equality condition I was given there that equal to condition was satisfied by two records 10 3 chord 23 chord this 10 is equal to 10 20 is equal to 20 matching but 30 40 these are not matching of data 30 40 is called the unmatching of data so your equation is going to retrieving only matching data because of equality condition so this is a simple way I can go to say what exactly equal I okay so now now this is the equation exam in the same example I want to take a non equal on non equation what is your we said this is equal to an example where has come to non equation suppose if I want to go for non equally enjoy suppose if I want to go for my Nanak we join same taking here in non equation what is happened you can see the corium will try to now select the star from select the star from same table test 1 comma test too bad I wanted to say your test 1 dot serial number this greater than test 2 dot c LM okay so why I'm taking serial number okay means the two column data types are same so that's why I am taking to same column data types Here I am taking so the sessions we can understand in my previous videos so test one serial number greater than test two serial and this now I'm going to execute that is what I am doing here I'm not using any equal condition but except equal condition I can use it like greater than less than less than or equal to greater than or equals to any kind of things you can use but don't use equal except equal you can use any operator in your economically ensuring okay so now I'm going to execute you now what does happen you can see yes so I got to some output when Nani creature but your observed do you find out here in equi-join when what we are getting the output in that output we are only getting equality and matching data only we are getting but this time what has happened not matching data we are getting unmatching the it also so now we can see so it my first table test one okay it my second table test two and now what I'm asking test two one serial number is greater than test two serial number so now if you want to come this is test one and this is test two now you can compare this condition what are the condition was given here this condition can shake so now serial number now I just go for C I'm taking here I just hide these columns because these columns are not necessary for us just it is I just hiding this is what now in this two what has happened I will be picking here this is test one table information this is test two table information in between these two what I'm using greater than symbol okay so now we can see now so twenty first three columns is checking year twenty is greater than ten condition to or false yes condition is true condition is true next one that T is greater than ten condition to or false yes it is also true condition true so next one that T is greater than twenty condition true or false yes it is also true so that means what happened whatever the output was came whatever the output was came okay in this output if you see as per my given condition in my ich in non-equal join the same condition will be implemented here okay so every T 1 that means for test on table serial number is greater than two destitute serial numbers yes or no yes so 20 is greater than 10 so don't be is a bigger than 10 that is a bigger than 10 that is a bigger than 20 so that means whatever the values are satisfied for the given condition that values are displayed okay so this is what we called a non equal mechanism so these are the two joints okay equi and non equally so simple way I will explain your simple programs I was taken here to how to take your so equi-join equal operator only I can use so non equally in place of suppose in place of greater than I can use what now less than also if I'm using less than to execute this one what is my output this time and we can see I'm using what now 10 is 10 less than 20 satisfy 10 less than 40 is satisfied 10 20 less than 4 dissatisfied 30 less than 40 satisfied so that means you can use any operator condition in Nanako but don't use the Equality condition if you use equality condition then that is comes under equal this is equal and non equal joints differences thank you thank you for watching this video [Music]

Channel: Naresh i Technologies

Views: 46,232

Keywords: SQL, MSSQL, MSSQL Training, SQL Training, SQL Server Tutorials, SQL Server Course, Sudhakar L, sql training videos, sql server training, sql server tutorial videos, SQL Server, Equi Join, Equi Join and Non Equi Join, Non Equi Join in SQL Server, Equi Join and Non Equi Join in SQL, Equi Join and Non Equi Join in SQL Server, Difference between Equi Join and Non Equi Join in SQL, Difference between Equi Join and Non Equi Join, Equi Join in SQL Server

Id: pmfATg5ciWQ

Length: 16min 46sec (1006 seconds)

Published: Thu Jan 25 2018