أنشئ حسابًا أو سجّل الدخول للانضمام إلى مجتمعك المهني.
This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:
SELECT<select_list>FROM Table_A AINNERJOIN Table_B BON A.Key = B.Key
This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. This Join is written as follows:
SELECT<select_list>FROM Table_A ALEFTJOIN Table_B BON A.Key = B.Key
This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table. This Join is written as follows:
SELECT<select_list>FROM Table_A ARIGHTJOIN Table_B BON A.Key = B.Key
This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:
SELECT<select_list>FROM Table_A AFULLOUTERJOIN Table_B BON A.Key = B.Key
source
Cheers!
Simple Example: Lets say you have a Students table, and a Lockers table.
Each student can be assigned to a locker, so there is a "LockerNumber" column in the student table. More than one student could potentially be in a single locker, but especially at the begining of the school year, you may have some incoming students without lockers and some lockers that have no students assigned.
For the sake of this example, lets say you have 100 students,70 of which have lockers. You have a total of 50 lockers,40 of which have at least1 student and10 lockers have no student.
INNER JOIN is equivalent to "show me all students with lockers". Any students without lockers, or any lockers without students are missing.Returns70 rows
LEFT OUTER JOIN would be "show me all students, with their corresponding locker if they have one". This might be a general student list, or could be used to identify students with no locker. Returns100 rows
RIGHT OUTER JOIN would be "show me all lockers, and the students assigned to them if there are any". This could be used to identify lockers that have no students assigned, or lockers that have too many students. Returns80 rows (list of70 students in the40 lockers, plus the10 lockers with no student)
FULL OUTER JOIN would be silly and probably not much use. Something like "show me all students and all lockers, and match them up where you can" Returns110 rows (all100 students, including those without lockers. Plus the10 lockers with no student)
CROSS JOIN is also fairly silly in this scenario. It doesn't use the linked "lockernumber" field in the students table, so you basically end up with a big giant list of every possible student-to-locker pairing, whether or not it actually exists.Returns5000 rows (100 students x50 lockers). Could be useful (with filtering) as a starting point to match up the new students with the empty lockers.
Please read full debat: http://stackoverflow.com/questions/448023/what-is-the-difference-between-left-right-outer-and-inner-joins
Othere related article:
- http://javacodeimpl.blogspot.com/2013/08/difference-between-inner-join-right.html#.U7-JN0DCeCo
INNER JOIN gets all records that are common between both tables based on the foreign key
LEFT JOIN gets all records from the LEFT linked table but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL
RIGHT JOIN is like the above but gets all records in the RIGHT table
FULL JOIN gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table
if you have relation between two or more tables and you want those record which are in relation so u can use inner join and you want all record of parent table and mapping record in child table so you can use left ot right join in this join those record of parent table which have no mapping record null will appear in child table with these records and outer join get all records in parent and in child also those record which have no relation null will appear front of that record it can b parent or child
The differences...
The picture above is from here: http://www.cnblogs.com/ykt/p/3242583.html
Sorry, bayt doesn't let me post in Arabic, I had to type in English.
