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

Question

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

Answer 1

Inner join.

A join is combining the rows from two tables. An inner join attempts to match up the two tables based on the criteria you specify in the query, and only returns the rows that match. If a row from the first table in the join matches two rows in the second table, then two rows will be returned in the results. If there’s a row in the first table that doesn’t match a row in the second, it’s not returned; likewise, if there’s a row in the second table that doesn’t match a row in the first, it’s not returned.

Outer Join.

A left join attempts to find match up the rows from the first table to rows in the second table. If it can’t find a match, it will return the columns from the first table and leave the columns from the second table blank (null).

Answer 2

Having criticized the much-loved red-shaded Venn diagram, I thought it only fair to post my own attempt.

Although @Martin Smith's answer is the best of this bunch by a long way, his only shows the key column from each table, whereas I think ideally non-key columns should also be shown.

The best I could do in the half hour allowed, I still don't think it adequately shows that the nulls are there due to absence of key values in TableB or that OUTER JOIN is actually a union rather than a join:

Answer 3

left join on (aka left outer join on) returns inner join on rows union all unmatched left table rows extended by nulls.

right join (on aka right outer join on) returns inner join on rows union all unmatched right table rows extended by nulls.

full join on (aka full outer join on) returns inner join on rowsunion all unmatched left table rows extended by nulls union all unmatched right table rows extended by nulls.

(SQL Standard 2006 SQL/Foundation 7.7 Syntax Rules 1, General Rules 1 b, 3 c & d, 5 b.)

So don't outer join until you know what underlying inner join is involved.

---

Find out what rows inner join returns.

Read my comments there re the many confused & poor answers.

Then read my comments here re the many confused & poor answers.

Answer 4

A inner join only shows rows if there is a matching record on the other (right) side of the join.

A (left) outer join shows rows for each record on the left hand side, even if there are no matching rows on the other (right) side of the join. If there is no matching row, the columns for the other (right) side would show NULLs.

Answer 5

The answer is in the meaning of each one, so in the results.

Note :
In SQLite there is no RIGHT OUTER JOIN or FULL OUTER JOIN.
And also in MySQL there is no FULL OUTER JOIN.

My answer is based on above Note.

When you have two tables like these:

--[table1]               --[table2]
id | name                id | name
---+-------              ---+-------
1  | a1                  1  | a2
2  | b1                  3  | b2

---

CROSS JOIN / OUTER JOIN :
You can have all of those tables data with CROSS JOIN or just with , like this:

SELECT * FROM table1, table2
--[OR]
SELECT * FROM table1 CROSS JOIN table2

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2
1  | a1   | 3  | b2
2  | b1   | 1  | a2
2  | b1   | 3  | b2

---

INNER JOIN :
When you want to add a filter to above results based on a relation like table1.id = table2.id you can use INNER JOIN:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
--[OR]
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2

---

LEFT [OUTER] JOIN :
When you want to have all rows of one of tables in the above result -with same relation- you can use LEFT JOIN:
(For RIGHT JOIN just change place of tables)

SELECT * FROM table1, table2 WHERE table1.id = table2.id 
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
--[OR]
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id   | name 
---+------+------+------
1  | a1   | 1    | a2
2  | b1   | Null | Null

---

FULL OUTER JOIN :
When you also want to have all rows of the other table in your results you can use FULL OUTER JOIN:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
UNION ALL
SELECT Null, Null, * FROM table2 WHERE Not table2.id In (SELECT id FROM table1)
--[OR] (recommended for SQLite)
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION ALL
SELECT * FROM table2 LEFT JOIN table1 ON table2.id = table1.id
WHERE table1.id IS NULL
--[OR]
SELECT * FROM table1 FULL OUTER JOIN table2 On table1.id = table2.id

--[Results:]
id   | name | id   | name 
-----+------+------+------
1    | a1   | 1    | a2
2    | b1   | Null | Null
Null | Null | 3    | b2

Well, as your need you choose each one that covers your need ;).

Answer 6

I don't see much details about performance and optimizer in the other answers.

Sometimes it is good to know that only INNER JOIN is associative which means the optimizer has the most option to play with it. It can reorder the join order to make it faster keeping the same result. The optimizer can use the most join modes.

Generally it is a good practice to try to use INNER JOIN instead of the different kind of joins. (Of course if it is possible considering the expected result set.)

There are a couple of good examples and explanation here about this strange associative behavior:

• Are left outer joins associative?
• Does the join order matter in SQL?