What is the difference between Left, Right, Outer and Inner Joins?
SqlDatabaseJoinSql Problem Overview
I am wondering how to differentiate all these different joins ...
Sql Solutions
Solution 1  Sql
Simple Example: Let's say you have a Students
table, and a Lockers
table. In SQL, the first table you specify in a join, Students
, is the LEFT table, and the second one, Lockers
, is the RIGHT 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 beginning 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, let's say you have 100 students, 70 of which have lockers. You have a total of 50 lockers, 40 of which have at least 1 student and 10 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.
Returns 70 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.
Returns 100 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.
Returns 80 rows (list of 70 students in the 40 lockers, plus the 10 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"
Returns 110 rows (all 100 students, including those without lockers. Plus the 10 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 studenttolocker pairing, whether or not it actually exists.
Returns 5000 rows (100 students x 50 lockers). Could be useful (with filtering) as a starting point to match up the new students with the empty lockers.
Solution 2  Sql
There are three basic types of join:
INNER
join compares two tables and only returns results where a match exists. Records from the 1st table are duplicated when they match multiple results in the 2nd. INNER joins tend to make result sets smaller, but because records can be duplicated this isn't guaranteed.CROSS
join compares two tables and return every possible combination of rows from both tables. You can get a lot of results from this kind of join that might not even be meaningful, so use with caution.OUTER
join compares two tables and returns data when a match is available or NULL values otherwise. Like with INNER join, this will duplicate rows in the one table when it matches multiple records in the other table. OUTER joins tend to make result sets larger, because they won't by themselves remove any records from the set. You must also qualify an OUTER join to determine when and where to add the NULL values:LEFT
means keep all records from the 1st table no matter what and insert NULL values when the 2nd table doesn't match.RIGHT
means the opposite: keep all records from the 2nd table no matter what and insert NULL values whent he 1st table doesn't match.FULL
means keep all records from both tables, and insert a NULL value in either table if there is no match.
Often you see will the OUTER
keyword omitted from the syntax. Instead it will just be "LEFT JOIN", "RIGHT JOIN", or "FULL JOIN". This is done because INNER and CROSS joins have no meaning with respect to LEFT, RIGHT, or FULL, and so these are sufficient by themselves to unambiguously indicate an OUTER join.
Here is an example of when you might want to use each type:
INNER
: You want to return all records from the "Invoice" table, along with their corresponding "InvoiceLines". This assumes that every valid Invoice will have at least one line.OUTER
: You want to return all "InvoiceLines" records for a particular Invoice, along with their corresponding "InventoryItem" records. This is a business that also sells service, such that not all InvoiceLines will have an IventoryItem.CROSS
: You have a digits table with 10 rows, each holding values '0' through '9'. You want to create a date range table to join against, so that you end up with one record for each day within the range. By CROSSjoining this table with itself repeatedly you can create as many consecutive integers as you need (given you start at 10 to 1st power, each join adds 1 to the exponent). Then use the DATEADD() function to add those values to your base date for the range.
Solution 3  Sql
There are only 4 kinds:
 Inner join: The most common type. An output row is produced for every pair of input rows that match on the join conditions.
 Left outer join: The same as an inner join, except that if there is any row for which no matching row in the table on the right can be found, a row is output containing the values from the table on the left, with
NULL
for each value in the table on the right. This means that every row from the table on the left will appear at least once in the output.  Right outer join: The same as a left outer join, except with the roles of the tables reversed.
 Full outer join: A combination of left and right outer joins. Every row from both tables will appear in the output at least once.
A "cross join" or "cartesian join" is simply an inner join for which no join conditions have been specified, resulting in all pairs of rows being output.
Thanks to RusselH for pointing out FULL joins, which I'd omitted.
Solution 4  Sql
SQL JOINS difference:
Very simple to remember :
INNER JOIN
only show records common to both tables.
OUTER JOIN
all the content of the both tables are merged together either they are matched or not.
LEFT JOIN
is same as LEFT OUTER JOIN
 (Select records from the first (leftmost) table with matching right table records.)
RIGHT JOIN
is same as RIGHT OUTER JOIN
 (Select records from the second (rightmost) table with matching left table records.)
Solution 5  Sql
Check out Join (SQL) on Wikipedia

Inner join  Given two tables an inner join returns all rows that exist in both tables

left / right (outer) join  Given two tables returns all rows that exist in either the left or right table of your join, plus the rows from the other side will be returned when the join clause is a match or null will be returned for those columns

Full Outer  Given two tables returns all rows, and will return nulls when either the left or right column is not there

Cross Joins  Cartesian join and can be dangerous if not used carefully
Solution 6  Sql
Making it more visible might help. One example:
Table 1:
ID_STUDENT STUDENT_NAME
1 Raony
2 Diogo
3 Eduardo
4 Luiz
Table 2:
ID_STUDENT LOCKER
3 l1
4 l2
5 l3
What I get when I do:
Inner join of Table 1 and Table 2:
 Inner join returns both tables merged only when the key
(ID_STUDENT) exists in both tables
ID_STUDENT STUDENT_NAME LOCKER
3 Eduardo l1
4 Luiz l2
Left join of Table 1 and Table 2:
 Left join merges both tables with all records form table 1, in
other words, there might be nonpopulated fields from table 2
ID_ESTUDANTE NOME_ESTUDANTE LOCKER
1 Raony 
2 Diogo 
3 Eduardo l1
4 Luiz l2
Right join of table 1 and table 2:
 Right join merges both tables with all records from table 2, in
other words, there might be nonpopulated fields from table 1
ID_STUDENT STUDENT_NAME LOCKER
3 Eduardo l1
4 Luiz l2
5  l3
Outter join of table 1 and table 2:
 Returns all records from both tables, in other words, there
might be nonpopulated fields either from table 1 or 2.
ID_STUDENT STUDENT_NAME LOCKER
1 Raony 
2 Diogo 
3 Eduardo l1
4 Luiz l2
5  l3
Solution 7  Sql
LEFT JOIN
and RIGHT JOIN
are types of OUTER JOIN
s.
INNER JOIN
is the default  rows from both tables must match the join condition.
Solution 8  Sql
Inner join: Only show rows, when has it data from both of the tables.
Outer join: (left/right): Show the all result from the left / right table with the paired row(s), if it exists or not.
Solution 9  Sql
At first you have to understand what does join do? We connect multiple table and get specific result from the joined tables. The simplest way to do this is cross join.
Lets say tableA has two column A and B. And tableB has three column C and D. If we apply cross join it will produce lot of meaningless row. Then we have to match using primary key to get actual data.
Left: it will return all records from left table and matched record from right table.
Right: it will return opposite to Left join. It will return all records from right table and matched records from left table.
Inner: This is like intersection. It will return only matched records from both table.
Outer: And this is like union. It will return all available record from both table.
Some times we don't need all of the data, and also we should need only common data or records. we can easily get it using these join methods. Remember left and right join also are outer join.
You can get all records just using cross join. But it could be expensive when it comes to millions of records. So make it simple by using left, right, inner or outer join.
thanks