What is the difference between “INNER JOIN” and “O

Question

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Answer 1

What is the difference between “INNER JOIN” and “OUTER JOIN”?

They are the most commonly used existential operators in SQL, where INNER JOIN is used for 'exists' and LEFT OUTER JOIN is used for 'does not exist'.

Consider these queries:

users who have posted and have votes
users who have posted but have no badges

People who look for set-based solutions (an industry term) would recognise the respective queries as:

users who have posted INTERSECT users who have votes
users who have posted MINUS users who have badges

Translating these into standard SQL:

SELECT UserId FROM Posts
INTERSECT 
SELECT UserId FROM Votes;

SELECT UserId FROM Posts
EXCEPT 
SELECT UserId FROM Badges;

Others will think along similar lines of set inclusion:

users who have posted and IN the set of users who have votes
users who have posted and NOT IN the set of users who have badges

Translating these into standard SQL:

SELECT UserId 
  FROM Posts
 WHERE UserId IN ( SELECT UserId FROM Votes );

SELECT UserId 
  FROM Posts
 WHERE UserId NOT IN ( SELECT UserId FROM Badges );

Some will think in terms of 'existance' within sets e.g.

users who have posted and EXIST in the set of users who have votes
users who have posted and do NOT EXIST in the set of users who have badges

Translating these into standard SQL (note we now need to use range variables i.e. p, v, b):

SELECT p.UserId 
  FROM Posts p
 WHERE EXISTS ( SELECT *
                  FROM Votes v
                 WHERE v.UserId = p.UserId );

SELECT p.UserId 
  FROM Posts p
 WHERE NOT EXISTS ( SELECT *
                      FROM Badges b
                     WHERE b.UserId = p.UserId );

However, I've found that the "industry standard" approach is to exclusively use joins. I don't know what the thinking is here (Law of the Instrument? Premature optimization?), so I'll go straight to the syntax:

SELECT p.UserId 
  FROM Posts p
       INNER JOIN Votes v ON v.UserId = p.UserId;

SELECT p.UserId 
  FROM Posts p
       LEFT JOIN Badges b ON b.UserId = p.UserId
 WHERE b.UserId IS NULL;

Things to note:

• The only projection is from Users but we still need all those range variables (p, v, b) for search conditions.
• The UserId IS NULL search condition 'belongs' to the the OUTER JOIN but is disconnected in the query.
• LEFT is the industry standard: professionals will rewrite a query to avoid using RIGHT!
• The OUTER keyword from LEFT OUTER JOIN is omitted.

Closing remarks:

Sometimes joins are used in queries solely to determine whether values exist or do not exists in another set. Learn to look carefully at the attributes being projected (the columns in the SELECT clause): if there are none from the joined table then they are simply being used as existential operators. Additionally for outer join, look for instances of <key_column> IS NULL in the WHERE clause.

Answer 2

The Venn diagrams don't really do it for me.

They don't show any distinction between a cross join and an inner join, for example, or more generally show any distinction between different types of join predicate or provide a framework for reasoning about how they will operate.

There is no substitute for understanding the logical processing and it is relatively straightforward to grasp anyway.

1. Imagine a cross join.
2. Evaluate the on clause against all rows from step 1 keeping those where the predicate evaluates to true
3. (For outer joins only) add back in any outer rows that were lost in step 2.

(NB: In practice the query optimiser may find more efficient ways of executing the query than the purely logical description above but the final result must be the same)

I'll start off with an animated version of a full outer join. Further explanation follows.

---

Explanation

Source Tables

First start with a CROSS JOIN (AKA Cartesian Product). This does not have an ON clause and simply returns every combination of rows from the two tables.

SELECT A.Colour, B.Colour FROM A CROSS JOIN B

Inner and Outer joins have an "ON" clause predicate.

• Inner Join. Evaluate the condition in the "ON" clause for all rows in the cross join result. If true return the joined row. Otherwise discard it.
• Left Outer Join. Same as inner join then for any rows in the left table that did not match anything output these with NULL values for the right table columns.
• Right Outer Join. Same as inner join then for any rows in the right table that did not match anything output these with NULL values for the left table columns.
• Full Outer Join. Same as inner join then preserve left non matched rows as in left outer join and right non matching rows as per right outer join.

Some examples

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour

The above is the classic equi join.

Animated Version

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN ('Green','Blue')

The inner join condition need not necessarily be an equality condition and it need not reference columns from both (or even either) of the tables. Evaluating A.Colour NOT IN ('Green','Blue') on each row of the cross join returns.

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON 1 =1

The join condition evaluates to true for all rows in the cross join result so this is just the same as a cross join. I won't repeat the picture of the 16 rows again.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour

Outer Joins are logically evaluated in the same way as inner joins except that if a row from the left table (for a left join) does not join with any rows from the right hand table at all it is preserved in the result with NULL values for the right hand columns.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL

This simply restricts the previous result to only return the rows where B.Colour IS NULL. In this particular case these will be the rows that were preserved as they had no match in the right hand table and the query returns the single red row not matched in table B. This is known as an anti semi join.

It is important to select a column for the IS NULL test that is either not nullable or for which the join condition ensures that any NULL values will be excluded in order for this pattern to work correctly and avoid just bringing back rows which happen to have a NULL value for that column in addition to the un matched rows.

SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour

Right outer joins act similarly to left outer joins except they preserve non matching rows from the right table and null extend the left hand columns.

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour

Full outer joins combine the behaviour of left and right joins and preserve the non matching rows from both the left and the right tables.

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON 1 = 0

No rows in the cross join match the 1=0 predicate. All rows from both sides are preserved using normal outer join rules with NULL in the columns from the table on the other side.

SELECT COALESCE(A.Colour, B.Colour) AS Colour FROM A FULL OUTER JOIN B ON 1 = 0

With a minor amend to the preceding query one could simulate a UNION ALL of the two tables.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour = 'Green'

Note that the WHERE clause (if present) logically runs after the join. One common error is to perform a left outer join and then include a WHERE clause with a condition on the right table that ends up excluding the non matching rows. The above ends up performing the outer join...

... And then the "Where" clause runs. NULL= 'Green' does not evaluate to true so the row preserved by the outer join ends up discarded (along with the blue one) effectively converting the join back to an inner one.

If the intention was to include only rows from B where Colour is Green and all rows from A regardless the correct syntax would be

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour AND B.Colour = 'Green'

SQL Fiddle

See these examples run live at SQLFiddle.com.

Answer 3

I recommend Jeff's blog article. The best description I've ever seen, plus there is a visualization, e.g.:

Inner Join:

Full Outer Join:

Answer 4

In simple words :

Inner join -> Take ONLY common records from parent and child tables WHERE primary key of Parent table matches Foreign key in Child table.

Left join ->

pseudo code

1.Take All records from left Table
2.for(each record in right table,) {
    if(Records from left & right table matching on primary & foreign key){
       use their values as it is as result of join at the right side for 2nd table.
    } else {
       put value NULL values in that particular record as result of join at the right side for 2nd table.
    }
  }

Right join : Exactly opposite of left join . Put name of table in LEFT JOIN at right side in Right join , you get same output as LEFT JOIN.

Outer join : Show all records in Both tables No matter what. If records in Left table are not matching to right table based on Primary , Forieign key , use NULL value as result of join .

Example :

Lets assume now for 2 tables

1.employees , 2.phone_numbers_employees

employees : id , name 

phone_numbers_employees : id , phone_num , emp_id   

Here , employees table is Master table , phone_numbers_employees is child table(it contains emp_id as foreign key which connects employee.id so its child table.)

Inner joins

Take the records of 2 tables ONLY IF Primary key of employees table(its id) matches Foreign key of Child table phone_numbers_employees(emp_id).

So query would be :

SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Here take only matching rows on primary key = foreign key as explained above.Here non matching rows on primary key = foreign key are skipped as result of join.

Left joins :

Left join retains all rows of the left table, regardless of whether there is a row that matches on the right table.

SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Outer joins :

SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Diagramatically it looks like :

Answer 5

Inner joins require that a record with a related ID exist in the joined table.

Outer joins will return records for the left side even if nothing exists for the right side.

For instance, you have an Orders and an OrderDetails table. They are related by an "OrderID".

Orders

• OrderID
• CustomerName

OrderDetails

• OrderDetailID
• OrderID
• ProductName
• Qty
• Price

The request

SELECT Orders.OrderID, Orders.CustomerName
  FROM Orders 
 INNER JOIN OrderDetails
    ON Orders.OrderID = OrderDetails.OrderID

will only return Orders that also have something in the OrderDetails table.

If you change it to OUTER LEFT JOIN

SELECT Orders.OrderID, Orders.CustomerName
  FROM Orders 
  LEFT JOIN OrderDetails
    ON Orders.OrderID = OrderDetails.OrderID

then it will return records from the Orders table even if they have no OrderDetails records.

You can use this to find Orders that do not have any OrderDetails indicating a possible orphaned order by adding a where clause like WHERE OrderDetails.OrderID IS NULL.

Answer 6

Inner Join An inner join focuses on the commonality between two tables. When using an inner join, there must be at least some matching data between two (or more) tables that are being compared. An inner join searches tables for matching or overlapping data. Upon finding it, the inner join combines and returns the information into one new table.

Outer Join An outer join returns a set of records (or rows) that include what an inner join would return but also includes other rows for which no corresponding match is found in the other table.

There are three types of outer joins:

Left Outer Join (or Left Join) Right Outer Join (or Right Join) Full Outer Join (or Full Join) Each of these outer joins refers to the part of the data that is being compared, combined, and returned. Sometimes nulls will be produced in this process as some data is shared while other data is not.