CODEWITHSUNDEEP
sqljoin
leo
leo

Reputation: 1053

What's difference between inner join, outer join, left join and right join?

What's difference between inner join, outer join, left join and right join? Which one has the best performance?

Upvotes: 44

Views: 31008

Answers (4)

Christian C. Salvadó
Christian C. Salvadó

Reputation: 828002

Check this article:

A Visual Explanation of SQL Joins

Inner Join:

Left Outer Join:

Right Outer Join:

Upvotes: 81

Keith
Keith

Reputation: 155872

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.

Upvotes: 3

Michael Buen
Michael Buen

Reputation: 39463

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"  ));
        }

Upvotes: 3

HLGEM
HLGEM

Reputation: 96640

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).

Upvotes: 4

Related Questions