Search code examples
sqlrdbms

What are the uses of the different join operations?

What are the uses of the different join operations in SQL? Like I want to know why do we need the different inner and outer joins?

Solution

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