What is the difference between Left, Right, Outer and Inner Joins? [duplicate]

I am wondering how to differentiate all these different joins ...

Solution 1:

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 student-to-locker 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:

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 CROSS-joining 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:

There are only 4 kinds:

1. Inner join: The most common type. An output row is produced for every pair of input rows that match on the join conditions.
2. 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.
3. Right outer join: The same as a left outer join, except with the roles of the tables reversed.
4. 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.