What are the uses of the different join operations?

What are the uses of the different join operations in SQL? Like I want to kno开发者_运维百科w why do we need the different inner and outer joins?

The only type of join you really need is LEFT OUTER JOIN. Every other type of join can be rewritten in terms of one or more left outer joins, and possibly some filtering. So why do we need all the others? Is it just to confuse people? Wouldn't it be simpler if there were only one type of join?

You could also ask: Why have both a <= b and b >= a? Don't these just do the same thing? Can't we just get rid of one of them? It would simplify things!

Sometimes it's easier to swap <= to >= instead of swapping the arguments round. Similarly, a left join and a right join are the same thing just with the operands swapped. But again it's practical to have both options instead of requiring people to write their queries in a specific order.

Another thing you could ask is: In logic why do we have AND, OR, NOT, XOR, NAND, NOR, etc? All these can be rewritten in terms of NANDs! Why not just have NAND? Well it's awkward to write an OR in terms of NANDs, and it's not as obvious what the intention is - if you write OR, people know immediately what you mean. If you write a bunch of NANDs, it is not obvious what you are trying to achieve.

Similarly, if you want to do a FULL OUTER JOIN b you could make a left join and a right join, remove duplicated results, and then union all. But that's a pain and so there's a shorthand for it.

When do you use each one? Here's a simplified rule:

• If you always want a result row for each row in the LEFT table, use a LEFT OUTER JOIN.
• If you always want a result row for each row in the RIGHT table, use a RIGHT OUTER JOIN.
• If you always want a result row for each row in either table, use a FULL OUTER JOIN.
• If you only want a result row when there's a row in both tables, use an INNER JOIN.
• If you want all possible pairs of rows, one row from each table, use a CROSS JOIN.

inner join - joins rows from both sets of the match based on specified criteria.

outer join - selects all of one set, along with matching or empty (if not matched) elements from the other set. Outer joins can be left or right, to specify which set is returned in its entirety.

To make the other answers clearer - YOU GET DIFFERENT RESULTS according to the join you choose, when the columns you're joining on contain null values - for example.

So - for each Real-life scenario there is a join that suits it (either you want the lines without the data or not in the null values example).

My answer assumes 2 tables joined on a single key:

• INNER JOIN - get the results that are in both join tables (according to the join rule)
• FULL OUTER JOIN - get all results from both table (Cartesian product)
• LEFT OUTER JOIN - get all the results from left table and the matching results from the right

You can add WHERE clauses in order to further constrain the results.

Use these in order to only get what you want to get.