SQL Join Differences

Question

What's difference between inner join and outer join (left join,right join), and which has the best performance of them?

Thanks!

Answer 1

Check this article:

A Visual Explanation of SQL Joins

Inner Join:

Left Outer Join:

Right Outer Join:

Answer 2

Performance comparisions between types of joins are irrelevant as they give differnt results sets. Even if an inner join is faster you wouldn't use it if you needed the results of a left join (which includes even the records which don't match the second table in the join).

Answer 3

A LEFT JOIN B is the same as B RIGHT JOIN A. Some RDBMS don't have RIGHT JOIN, so you have to rewrite your RIGHT JOIN logic to LEFT JOIN logic

A 1 2 3
B 2 3 4 3

SELECT A.I FROM INNER JOIN B ON B.I = A.I;

output: 2 3, 3

SELECT A.I AS X, B.I AS Y FROM A LEFT JOIN B ON B.I = A.I;

read above code as A on LEFT, JOINs B

output: 

X Y
1 NULL
2 2
3 3
3 3

SELECT A.I AS X, B.I AS Y FROM B RIGHT JOIN A ON A.I = B.I;

Read the above code as B on RIGHT, JOINs A. Which is just the same as A is on LEFT

Whatever is on left, is always evaluated, always have an output. You can imagine A LEFT JOIN B, B RIGHT JOIN A as:

        var result = new Dictionary<int, int?>();

        var A = new int[] { 1, 2, 3 };
        var B = new int[] { 2, 3, 4, 3 };

        foreach (int aElem in A)
        {


            bool hasMatch = false;
            foreach (int bElem in B)
            {
                if (bElem == aElem)
                {
                    result.Add(aElem, bElem);
                    hasMatch = true;
                }
            }

            if (!hasMatch)
                result.Add(aElem, null);
        }



        foreach(int X in result.Keys)
        {
            MessageBox.Show(string.Format("X {0} Y {1}", X, result[X].HasValue ? result[X].Value.ToString() : "NULL"  ));
        }

Answer 4

Left, right, inner and outer don't affect performance, and they have been well explained here already.

However there are hints you can add to joins that do effect performance: hash, loop and merge.

Normally the query planner decides which of these to do, but occasionally you can improve performance by overriding it.

A loop join goes through every row in the second table for each row in the first. This is good if you have one very big table and one much smaller.

A merge join goes through both tables together in order. It can be very quick if both tables are already ordered by the field that you're joining on.

A hash join uses lots of temporary tables to group the output as it sorts through the joined data.

Some specialist DBs also supports other types, such as bitmap joins.

Answer 5

Hopefully you understand the pictures. Performance-wise, they are equivalent - no difference.

EDIT: Oops. Guess you didn't care about that part of the answer.